Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: [RESOLVED] DAO : Function Replace() & Access 2000

    Hello,

    I want to modify an old application which is dev with DAO

    I 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.6

    The error is 3080 function non define.

    My dev :
    vSQL= "Select ... From ... Order by Replace(MyField, '''', ''' ')"
    set vRs=db.OpenRecordset(vSQL,dbOpenDynaset) >>>the error is here

    The same query without Replace() is OK

    Have you got an idee to do that?

    Thx.
    Last edited by antidotes; 09-28-05 at 11:06.

  2. #2
    Join Date
    May 2005
    Posts
    150
    "SELECT ....Order by Replace(MyField, '''', ''' ')" <--Error is here

    Can't use Quote marks within VBA code use ' instead

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Antidotes

    I can confirm that i can replicate your problem but, annoyingly for you, I can't figure a solution.

    Works in A02 DAO 3.6 - not in VB6 DAO 3.6 - Replace is an unrecognised function. I've tried Jet & ODBC workspaces and still no joy.

    Sorry chief - can't really google owt either
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2005
    Posts
    150
    Replace(MyField,'',''')

    Try that instead

  5. #5
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Other Possible Problem

    When you move from an older Access version, the default was DAO and the newer (after 2000) the default is ADO.

    Therefore the following change from:
    Code:
    Dim dbs as database, rcs as recordset
    to:
    Code:
    Dim dbs as DAO.database, rcs as DAO.recordset
    look at your code and see if these are right!

    DBS4M

  6. #6
    Join Date
    Sep 2005
    Posts
    5
    Thx all for your answer but it's not ok

    @Kasco
    It's not the ' the problem but the replace() Function
    The error msg is "Execution error 3085 : 'replace' function is not define in the expression

    With you expression :Replace(MyField,'',''') the error is a syntax error

    @dbsupport4me
    I have try this but the error is exactly the same

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - ADO won't have it either - same error.

    I would expect WaynePhilips or IzyRider to have some sort of perspective on this. M Owen might know something from the ADO side of things that might also be relevent to your DAO code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - looks like you will need to get creative:

    http://www.pcreview.co.uk/forums/thread-2040372.php

    Hmm - I tried creating a QueryDef object, appending and running that but still the same error. Running short of ideas... perhaps a combination of Iif and instr?
    Last edited by pootle flump; 09-28-05 at 06:53.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I checked Iif and Instr - both work fine.

    Assuming there is a maximum of one apostrophe per entry you can use these. If it is always the second character then even better.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    interesting...
    workaround that flies for me in A2K:

    in a global module:
    Public Function fixSort(myString As String) As String
    fixSort = Replace(myString, "'", " ")
    End Function

    and then
    SELECT * FROM myTable ORDER BY fixSort(myApostropheField)

    ...not yet obvious to me why replace() won't work directly in a query.


    izy


    LATER: forgot to mention - object browser tells me that replace() is part of the VBA library so it shouldn't really care about ADO/DAO stuff
    Last edited by izyrider; 09-28-05 at 09:02.
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Izy

    Long time no see - you still jetting around on business trips then?

    This is DAO code executed from VB6 - you can't call a UD function either. I'm not sure there is a fix other than using the functions that are available to simulate the replace function

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2005
    Posts
    5
    @izy
    I have try to do this but the error is the same.

    @pootle flump
    Have you got a sample to Iif and Instr please?
    My english is bad and i haven't understood this

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - an example would be:

    Code:
    SELECT ApostField
    FROM ApostT
    ORDER BY
     IIf(InStr([ApostField],Chr(39))>0,Mid([ApostField],1,InStr([ApostField],Chr(39))-1) & ' ' & Mid([ApostField],InStr([ApostField],Chr(39))+1),[ApostField])
    It assumes that there is only one apostrophe. You are in all sorts of trouble otherwise.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2005
    Posts
    5
    Very very very thx !!!!
    It's ok

    Yeahhhhh !!!!!

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Izy - as promised a wee demo (hastily knocked up of course). You'll need to pop the db on the root of C - I couldn't remember the vb6 equivalent of CurrentDb.Name to read the source directory....

    The exe can be anywhere.
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •