Telephone Number Format

[Guest]

Have table of about 11,000 records. Table design was NOT set up to store
phone numbers without format. They have been entered and stored in (333)
444-5555 format. Query works fine if they are asked for in the displayed and
stored format. The users fumble with the correct entry for the query and
would rather just key in the raw 10 digit sequence. ( ie 3334445555) for the
query. I have changed the field definitions in the table design, but that is
not fixing the stored data. If I go back and retype a phone number in, that
entry becomes accesible to the raw number query. How could I automate or fix
in some other fashion how these phone numbers were previously stored?

Thanks for your great help.( I know the answer is out there!)

 

[Jack MacDonald]

I would work on a COPY of the database and update the field
incrementally with a series of Update queries.

- Using the query grid, set a criterion where Mid(PhoneNumber,1,1)="("
- satisfy yourself that the selected records are indeed the ones that
you want to modify
- change the query to an Update query while maintaining the same
selection criterion
- update PhoneNumber field with an expression: Mid(PhoneNumber,2)
- revert to a Select query and confirm that none of the records
commence with "("

modify the criterion to find ")" in position 4
- etc
- replace the existing PhoneNumber with Left(PhoneNumber,3) &
Mid(PhoneNumber,5)

modify the criterion to find "-" in position 4
- etc

modify the criterion for Instr(1,PhoneNumber,"(")>0 OR
Instr(1,PhoneNumber,")")>0 OR Instr(1,PhoneNumber,"-")>0 to confirm
that you have found all of the extraneous characters.

Have table of about 11,000 records. Table design was NOT set up to store
phone numbers without format. They have been entered and stored in (333)
444-5555 format. Query works fine if they are asked for in the displayed and
stored format. The users fumble with the correct entry for the query and
would rather just key in the raw 10 digit sequence. ( ie 3334445555) for the
query. I have changed the field definitions in the table design, but that is
not fixing the stored data. If I go back and retype a phone number in, that
entry becomes accesible to the raw number query. How could I automate or fix
in some other fashion how these phone numbers were previously stored?

Thanks for your great help.( I know the answer is out there!)

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Peter R. Fletcher]

I agree about working on a COPY of the database, but that's an awful
lot of work to handle a fairly simple substitution/update! Your
approach guarantees that you will only change "perfectly formed" phone
numbers in the old format, but I would regard that as a negative
rather than a positive.

I would write a very simple Function to strip out all non-numerics
from a passed string, e.g.

Function MyStrip(strInput As String) As String

Dim strTemp As String
Dim strChar As String
Dim intCount As Integer

strTemp = Trim(strInput)
MyStrip = vbNullString

For intCount = 1 To Len(strTemp)
strChar = Mid$(strTemp, 1, 1)
If IsNumeric(strChar) Then MyStrip = MyStrip & strChar
strTemp = Mid$(strTemp, 2)
Next intCount

End Function

I would then use a single Update Query to replace the old format phone
numbers with MyStrip(OldNumber). No data will be lost - indeed, if the
old number was malformed in a way that did not affect its numeric
content (extra spaces, missing parens, etc) it will be converted
correctly. Missing numbers will not be replaced, of course (!), but
they wouldn't be by any "automatic" approach.

I would work on a COPY of the database and update the field
incrementally with a series of Update queries.

- Using the query grid, set a criterion where Mid(PhoneNumber,1,1)="("
- satisfy yourself that the selected records are indeed the ones that
you want to modify
- change the query to an Update query while maintaining the same
selection criterion
- update PhoneNumber field with an expression: Mid(PhoneNumber,2)
- revert to a Select query and confirm that none of the records
commence with "("

modify the criterion to find ")" in position 4
- etc
- replace the existing PhoneNumber with Left(PhoneNumber,3) &
Mid(PhoneNumber,5)

modify the criterion to find "-" in position 4
- etc

modify the criterion for Instr(1,PhoneNumber,"(")>0 OR
Instr(1,PhoneNumber,")")>0 OR Instr(1,PhoneNumber,"-")>0 to confirm
that you have found all of the extraneous characters.




**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Jack MacDonald]

Much better...

I agree about working on a COPY of the database, but that's an awful
lot of work to handle a fairly simple substitution/update! Your
approach guarantees that you will only change "perfectly formed" phone
numbers in the old format, but I would regard that as a negative
rather than a positive.

I would write a very simple Function to strip out all non-numerics
from a passed string, e.g.

Function MyStrip(strInput As String) As String

Dim strTemp As String
Dim strChar As String
Dim intCount As Integer

strTemp = Trim(strInput)
MyStrip = vbNullString

For intCount = 1 To Len(strTemp)
strChar = Mid$(strTemp, 1, 1)
If IsNumeric(strChar) Then MyStrip = MyStrip & strChar
strTemp = Mid$(strTemp, 2)
Next intCount

End Function

I would then use a single Update Query to replace the old format phone
numbers with MyStrip(OldNumber). No data will be lost - indeed, if the
old number was malformed in a way that did not affect its numeric
content (extra spaces, missing parens, etc) it will be converted
correctly. Missing numbers will not be replaced, of course (!), but
they wouldn't be by any "automatic" approach.




Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security