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
|