Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
antidotes
Starting Member
3 Posts |
 Posted - 2005-09-28 : 04:52:31
|
| Hello, I want to modify an old application which is dev with DAOI have a problem with the Sort and the Apostrope (')Sorted : - D'AB - DAC - D'AD - DAD - D'EB - DEC That I want : - D'AB - D'AD - D'EB - DAC - DAD - DEC My query is (This is correct in Access 2000 SP3)Select ... From ... Order by Replace(MonChamp, '''', ''' '); The sorted is OK with this query in Access 2000 SP3, but impossible to execute in VB6 with DAO 3.6The error is 3080 function non define.My dev : vSQL= "Select ... From ... Order by Replace(MyField, '''', ''' ')" set vRs=db.OpenRecordset(vSQL,dbOpenDynaset) >>>the error is hereThe same query without Replace() is OKHave you got an idee to do that? Thx. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-28 : 08:44:02
|
| Replace() is a VB function that the Access run-time evaluates; it is not a JET function that DAO evaluates. Thus, you cannot execute a SELECT w/ that function using only DAO, you must have Access running.you can try something likeORDER BY IIF(MId(MyField,2,1)="'",Left(MyField,1,1) & " " & Mid(MyField,3),MyField)Not as flexible as using Replace, but it handles an apostrophe in the 2nd position. |
 |
|
|
antidotes
Starting Member
3 Posts |
Posted - 2005-09-28 : 09:36:00
|
| Hi jsmith8858,Thx for your answer.I try this but in VB this syntax is incorrect for the SQL query :( |
|
|
|
antidotes
Starting Member
3 Posts |
Posted - 2005-09-28 : 10:29:22
|
| It's OK for each ' in the wordOk i Have test this query:SELECT MonChamp FROM MaTable ORDER BY IIf(InStr([MonChamp],Chr(39))>0,Mid([MonChamp],1,InStr([MonChamp],Chr(39))-1) & \' \' & Mid([MonChamp],InStr([MonChamp],Chr(39))+1),[MonChamp]) |
|
|
|
|
|
|
|
|
[Resolved] DAO : Function Replace() & Access 2000