Google Answers Logo
View Question
Q: Microsoft Access - Find & Replace for # symbol ( Answered 4 out of 5 stars,   0 Comments )
Subject: Microsoft Access - Find & Replace for # symbol
Category: Computers > Programming
Asked by: wolvies-ga
List Price: $5.00
Posted: 09 Jan 2003 03:33 PST
Expires: 08 Feb 2003 03:33 PST
Question ID: 139745
I am trying to delete all instances of # polluting my database (it
means something to those who entered the data but is useless for me).
I can use 'Find & Replace' for all other punctuation but if I put in #
it deletes all numbers not the actual sign. Is there a way to delete
all # signs in Find & Replace? If not can a simple query be run, or
does it require more complex SQL code ? Would appreciate knowing how
to solve this pesky problem !
Subject: Re: Microsoft Access - Find & Replace for # symbol
Answered By: hammer-ga on 09 Jan 2003 05:17 PST
Rated:4 out of 5 stars
There is a bug in Access 2000 which requires a small workaround to do
this. Replace cannot be called from a query, so you have to write a
little wrapper function.

Paste this code into a new Module and Save.

Public Function QReplace(strIn As String, strOld As String, strNew As
String) As String
    QReplace = Replace(strIn, strOld, strNew)
End Function

Then you can use this query to do your Update.

UPDATE tblTest SET tblTest.Test2 = QReplace([tblTest].[Test2],"#","");

Search Strategy: Use Google Groups Search
Access query REPLACE undefined

Good luck!

- Hammer

Request for Answer Clarification by wolvies-ga on 09 Jan 2003 08:31 PST
Thank you - we can see what that is trying to do and how it is trying
to work, but we appear to have Acces97 which doesn't seem to know what
Replace on its own is in VB. What would be the 97 version of this
query ? Thanks

Clarification of Answer by hammer-ga on 09 Jan 2003 10:22 PST
My apologies for the incorrect version. I thought I'd seen you using
Access 2000 in another question you posted. Anyway, here's the Access
97 version.

Paste this routine into a new module and Save.

 Function adhReplace(ByVal varValue As Variant, _
  ByVal strFind As String, ByVal strReplace As String) As Variant

     ' Replace all instances of strFind with strReplace in varValue.

     ' From Access 97 Developer's Handbook
     ' by Litwin, Getz, and Gilbert (Sybex)
     ' Copyright 1997.  All rights reserved.

     ' In:
     '    varValue: value you want to modify
     '    strFind: string to find
     '    strReplace: string to replace strFind with
     ' Out:
     '    Return value: varValue, with all occurrences of strFind
     '     replaced with strReplace.

     Dim intLenFind As Integer
     Dim intLenReplace As Integer
     Dim intPos As Integer

     If IsNull(varValue) Then
         adhReplace = Null
         intLenFind = Len(strFind)
         intLenReplace = Len(strReplace)

         intPos = 1
             intPos = InStr(intPos, varValue, strFind)
             If intPos > 0 Then
                 varValue = Left(varValue, intPos - 1) & _
                  strReplace & Mid(varValue, intPos + intLenFind)
                 intPos = intPos + intLenReplace
             End If
         Loop Until intPos = 0
     End If
     adhReplace = varValue
 End Function
'****************** Code End *****************

Run this query:

UPDATE Names SET Names.Name = adhReplace([Names].[Name],"#","");

- Hammer

Clarification of Answer by hammer-ga on 09 Jan 2003 15:25 PST
By the way, the Access 97 routine can also be found at the MVPS Access FAQ:

The code can be used freely, provided the header is not altered.

- Hammer

Request for Answer Clarification by wolvies-ga on 16 Jan 2003 08:54 PST
<i>Odd, I don't seem to be able to add a clarification

Just wanted to say thanks again - we used the routine on a second
batch of data and it worked fine.

All the best
Wolvies, lol

Clarification of Answer by hammer-ga on 16 Jan 2003 14:17 PST
Excellent. Glad to be of help.

- Hammer
wolvies-ga rated this answer:4 out of 5 stars
Thanks - funnily enough I haven't had chance to use it. We found the
simplest of work-arounds by exporting the table into Excel, using the
Find & Replace there to zap the #s and then reimport it. Thanks for
coming back though

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy