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
|
|||
|
|||
Test the length of a field
Hi all,
I'm using this (John Spencer) function and update query to remove non-numeric symbols from my phone field. It works brilliant, but now I need to test the length of this number. The problem is long distance numbers require a "1" but local numbers don't. I'm wondering if I shouldn’t have a table with local area codes that could be referenced? Is that a solid approach? UPDATE Contacts SET Contacts.Phone = fstripToNumbersOnly([Phone]) WHERE Phone Like "*[!0-9]*" 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 Thanks Richard |
#2
|
|||
|
|||
Test the length of a field
Since fstripToNumberOnly returns a string you should be able to test the
length of the string: Len(fstripToNumbersOnly([Phone])) Or perhaps better, since the function reduces the field to numbers only, test the length of the field when you are done running the function: Len([Phone]) I live in an state where there may be long-distance calls within the same area code. Do you have local calls to different area codes? "Richard" wrote in message ... Hi all, I'm using this (John Spencer) function and update query to remove non-numeric symbols from my phone field. It works brilliant, but now I need to test the length of this number. The problem is long distance numbers require a "1" but local numbers don't. I'm wondering if I shouldn't have a table with local area codes that could be referenced? Is that a solid approach? UPDATE Contacts SET Contacts.Phone = fstripToNumbersOnly([Phone]) WHERE Phone Like "*[!0-9]*" 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 Thanks Richard |
Thread Tools | |
Display Modes | |
|
|