Remove a character in Ms Access Query (or table)

[Guest]

How do I remove an "_" (underscore, or other character) in a variable without
removing numbers which are at the same position in the variable but in other
records?

 

[Douglas J. Steele]

Replace(VariableName, "_", "")

 

[raskew via AccessMonster.com]

Ben -
Try saving this function to a standard module then test as described. To use
in a query, you'd add (to remove letters "a" & "e"): chopit([YourFieldName],
"a","e") AS Expr1

Function ChopIt(pstr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Purpose: Remove a list of unwanted
' characters from a string
'Coded by: raskew
'Inputs: From debug window:
' 1) ? chopit("123-45-6789", "-")
' 2) ? chopit(" the quick brown fox ", " ", "o")
'Output: 1) 123456789
' 2) thequickbrwnfx
'*******************************************

Dim strHold As String
Dim i As Integer
Dim n As Integer

strHold = Trim(pstr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
Do While InStr(strHold, varmyvals(n)) > 0
i = InStr(strHold, varmyvals(n))
strHold = Left(strHold, i - 1) & Mid(strHold, i + Len(varmyvals(n)))

Loop
Next n
ChopIt = Trim(strHold)
End Function

HTH - Bob