Test the length of a field

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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
 
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?
 
Back
Top