Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17

    Unanswered: Replace function - shouldn't be too difficult!

    Hi, I'm trying to use the replace function but to no avail.

    When I do the following:
    ?replace("HELLO","ELLO","ello")
    I am returned with
    HELLO

    and when I try
    ?Replace("HELLO", "ELLO", "ello",,,vbTextCompare)
    I get the error "compile error: wrong number of arguments or invalid property assignment"

    Running access 2002, doing the above in the vba immediate window (it doesn't work through queries either)

    Please help!
    Chris

  2. #2
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    WOuldn't an IF statement work??

    If Hello change to Ello??

    Sorry Im not great on SQL, but you get my gist??

  3. #3
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Originally posted by Davekingwcp
    WOuldn't an IF statement work??

    If Hello change to Ello??

    Sorry Im not great on SQL, but you get my gist??
    Cheers, but that was just an example, I want to use the replace function in many different situations and only replace certain sections of text which would make that solution unmanagable.

    Chris

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by clhumphreys
    Cheers, but that was just an example, I want to use the replace function in many different situations and only replace certain sections of text which would make that solution unmanagable.

    Chris
    Do you want to use the Replace function in the Immediate window or under a module?

  5. #5
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Originally posted by hammbakka
    Do you want to use the Replace function in the Immediate window or under a module?
    I want to use it in a query, but if it aint gonna work in the immediate window surely it won't work anywhere else?

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    With Option Compare Database, using
    replace(Expression:="HELLO", Replace:="ello", Find:="ELLO", Compare:=vbTextCompare)

    my access returns 'Hello'.

    Does this not work for you in any database or just a specific database?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Originally posted by HomerBoo
    With Option Compare Database, using
    replace(Expression:="HELLO", Replace:="ello", Find:="ELLO", Compare:=vbTextCompare)

    my access returns 'Hello'.

    Does this not work for you in any database or just a specific database?
    Aah, tried it in a new blank database and it worked fine... so what's going on?

    Cheers,
    Chris

  8. #8
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Problem solved!

    Some guy in another group suggested there was another conflicting replace function that had been made, and there it was hiding away. God knows who put that there! )

    Cheers anyway,
    Chris

  9. #9
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    YIKES!!!

    Um, user-defined functions shouldn't really be named the same as intrinsic functions (reading your post, I know you didn't do this). If you had delimited Replace by its library location,

    ?vba.Strings.Replace ("HELLO", "ELLO", "ello")

    you would have got the result you wanted, but Access probably saw the user-defined one and assumed you wanted to use that one. I didn't know that user-defined functions were higher on the hierarchy than references!
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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