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 !

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
     Else
         intLenFind = Len(strFind)
         intLenReplace = Len(strReplace)

         intPos = 1
         Do
             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:
http://www.mvps.org/access/

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 Comment...no clarification
needed.</i>

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

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