If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
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 |
#2
|
|||
|
|||
Update Query
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 "Richard" wrote in message ... 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 |
#3
|
|||
|
|||
Update Query
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 -- Kind regards Noëlla "Richard" wrote: 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 |
#4
|
|||
|
|||
Update Query
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 Richard wrote: 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 |
#5
|
|||
|
|||
Update Query
"Richard" wrote in message
... 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) |
#6
|
|||
|
|||
Update Query
Thank you to all
I will try each suggestion, thanks again for your time. Richard "Dirk Goldgar" wrote: "Richard" wrote in message ... 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) |
Thread Tools | |
Display Modes | |
|
|