Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace Function in Query/VBA? (Access 2k, Win 2K Pro)

    I wonder if this is a rather common problem....

    I link a table from SQL Server that is receiving data from the Web. Due to the fact that SQL Server reads apostrophes as field deliminators, I used the Replace (Blah, "'", "''") function in ASP to massage the webform data so it will write to SQL Server. NOW, I pull the data into an Access table and the happy user sees O''Hannan instead of O'Hannan. I want to reverse the apostrophe replace operation at this point (in the Access table) and am stuck on how to do this. I thought, 'build a query and use the Replace function from the Expression wizard... Nope! Doesn't seem to exist. Then I think, run a UPDATE and implement the Replace function via VBA, and I guess that'd work but... that means connects and reads and seems like overhead.

    is there something easier I am missing?? Is there in fact a Replace function hidden somewhere in Access?

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win 2K Pro)

    Access 2000 VBA has a Replace function, but it cannot be used in expressions in queries. You can, however, write a custom "wrapper" function and use that in expressions in queries. See ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    hmmm... easy enough solution but I can't get it to work! with the suggested code from the link at MS,
    Function MyStrRev(strRev)
    MyStrRev = StrReverse(strRev)
    End Function
    does indeed reverse the text, but when I try 'hacking' to
    Function MyStrRev(strRev)
    MyStrRev = Replace(strRev, "''", "'")
    End Function
    no errors occur but nothing gets replaced. Unfortunately, the on-line help on using Replace() is a bit too sparse. Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    You're replacing two single quotes by one, instead of replacing a double quote by a single one. Try this:
    <code>
    Function MyStrRev(strRev)
    MyStrRev = Replace(strRev, """", "'")
    End Function
    </code>
    (to include a double quote within a quoted string, you must use TWO double quotes), or
    <code>
    Function MyStrRev(strRev)
    MyStrRev = Replace(strRev, Chr(34), Chr(39))
    End Function</code>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    thanks! that worked. The thing *looked* like two apostrophes...

    more bier for hans!
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    I would name the function MyReplace, by the way. MyStrRev as a wrapper for Replace is bound to cause confusion.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    already did that, but thanks for the cautionary note.

    BTW, do you know of a utility I can use to find old code that isn't attached to a db object? for example, if I delete a button with code behind, the code stays, which can (and does) lead to confusion.

    TIA

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Replace Function in Query/VBA? (Access 2k, Win

    See the replies to <post#=194190>post 194190</post#>.

Posting Permissions

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