Update Query

[Richard]

Hi all,

I import spreadsheets into a contacts style DB. I currently run update
queries to remove all the non numeric symbols like - ( ) . and spaces from
my phone field. My goal is to have a clean 10-digit phone number 9993334242.
I use this update query to remove hyphens.

UPDATE Contacts SET Contacts.Phone = Replace([Phone],"-","");

How can I remove all unwanted symbols with one click?

Thanks
Richard

 

[Sandy H]

You could probably write a function to call instead of using replace in your
query.

Eg
Public Function RemoveSymbols(sNumber as string) as Long

snumber = replace(sNumber,"-","")
snumber = replace(sNumber,"/","")
snumber = replace(sNumber," ","")
etc......

RemoveSymbols = sNumber

End Function

 

[NG]

Public Function gfgCompact(varText As Variant) As String
On Error GoTo Err_gfgCompact

Dim strText As String, strResult As String, strChar As String
Dim intPos As Integer, intReplace As Integer
Dim strReplace1 As String, strReplace2 As String, strRemove As String


strRemove = " ,?;.:/=+%*$[]&#'(§!{})°-_" & """"
strReplace1 = "ÃÀÂ@ÉÈÊËÇÃÖÜÙµ"
strReplace2 = "AAAAEEEECIOUUU"

If Nz(varText) = "" Then
gfgCompact = ""
Else
strText = UCase(varText)
End If
For intPos = 1 To Len(strText)
If InStr(1, strRemove, Mid(strText, intPos, 1)) = 0 Then
strChar = Mid(strText, intPos, 1)
intReplace = Nz(InStr(1, strReplace1, strChar))
If intReplace > 0 Then
strChar = Mid(strReplace2, intReplace, 1)
End If
strResult = strResult & strChar
End If
Next intPos
gfgCompact = strResult


Exit_gfgCompact:
Exit Function

Err_gfgCompact:
msgbox err.number & ": " & err.description
Resume Exit_gfgCompact


End Function

 

[John Spencer]

I use a function to do this.

Copy the function below, paste it into a VBA module and use it in your update
query.

UPDATE Contacts
SET Contacts.Phone = fstripToNumbersOnly([Phone])
WHERE Phone Like "*[!0-9]*"

Another option would be to nest replace functions, but that can get really
complicated really fast if you need more than a couple characters replaced.

UPDATE Contacts SET Contacts.Phone =
Replace(Replace(Replace(Replace([Phone],"-","")," ",""),"(",""),"(","")
WHERE Phone Like "*[!0-9]*"

I added the where clause so you would only process records where the field had
some characters that was not a number character.

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut
End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

Hi all,

I import spreadsheets into a contacts style DB. I currently run update
queries to remove all the non numeric symbols like - ( ) . and spaces
from
my phone field. My goal is to have a clean 10-digit phone number
9993334242.
I use this update query to remove hyphens.

UPDATE Contacts SET Contacts.Phone = Replace([Phone],"-","");

How can I remove all unwanted symbols with one click?

Here's yet another version of a function to return only the numeric digits
in a string:

'----- start of code -----
Function fncDigitsOnly(varOldNumber As Variant) As Variant

' Removes any non-numeric characters in a string, returning
' only the numeric digits. Returns Null if argument is Null.

Dim i As Integer
Dim intLength As Integer
Dim intDigits As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

If IsNull(varOldNumber) Then
fncDigitsOnly = Null
Exit Function
End If

strOldNumber = varOldNumber & vbNullString
intLength = Len(strOldNumber)
strNewNumber = strOldNumber

For i = 1 To intLength
strThisCharacter = Mid$(strOldNumber, i, 1)
Select Case Asc(strThisCharacter)
Case 48 To 57
intDigits = intDigits + 1
Mid$(strNewNumber, intDigits, 1) = strThisCharacter
End Select
Next i

fncDigitsOnly = Left$(strNewNumber, intDigits)

End Function
'----- end of code -----

You could call it in an update query:

UPDATE Contacts SET Phone = fncDigitsOnly(Phone)

 

[Richard]

Thank you to all

I will try each suggestion, thanks again for your time.

Richard

Hi all,

I import spreadsheets into a contacts style DB. I currently run update
queries to remove all the non numeric symbols like - ( ) . and spaces
from
my phone field. My goal is to have a clean 10-digit phone number
9993334242.
I use this update query to remove hyphens.

UPDATE Contacts SET Contacts.Phone = Replace([Phone],"-","");

How can I remove all unwanted symbols with one click?

Here's yet another version of a function to return only the numeric digits
in a string:

'----- start of code -----
Function fncDigitsOnly(varOldNumber As Variant) As Variant

' Removes any non-numeric characters in a string, returning
' only the numeric digits. Returns Null if argument is Null.

Dim i As Integer
Dim intLength As Integer
Dim intDigits As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

If IsNull(varOldNumber) Then
fncDigitsOnly = Null
Exit Function
End If

strOldNumber = varOldNumber & vbNullString
intLength = Len(strOldNumber)
strNewNumber = strOldNumber

For i = 1 To intLength
strThisCharacter = Mid$(strOldNumber, i, 1)
Select Case Asc(strThisCharacter)
Case 48 To 57
intDigits = intDigits + 1
Mid$(strNewNumber, intDigits, 1) = strThisCharacter
End Select
Next i

fncDigitsOnly = Left$(strNewNumber, intDigits)

End Function
'----- end of code -----

You could call it in an update query:

UPDATE Contacts SET Phone = fncDigitsOnly(Phone)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)