Phone Number Retrieval

[Scott]

I am having some issues with searching and retrieving phone numbers and the
information associated to the phone number.

Essentially what I would like to do is build a query that enables the user
to input a phone number and retrieve any associated data. When I set the
format for storing the data without symbols none of my stored data in the
table changes to the desired format. it remains in the form of the input mask
(000) 111-1111. In order to eliminate any search mistakes I want the user to
just input 0000000000. Is there a way to change the existing data to this
format but keep the input mask the same.

Thanks

Scott

 

[Arvin Meyer MVP]

The search must either be in the form of the data storage, or be able to be
converted to that form. If your input mask store the data with the symbols
and spaces, you'll need to change the data with an update query (after
changing the input mask to stop storing the characters), or you'll need to
input the same structure, or you'll need to write some code to change your
input to the form that you've stored.

 

[Scott]

Thanks Arvin,

I know how to update using a query if your changing the data entirely but
I'm not siure how to update the data in my field to just the format of the
input mask without changing the field values themselves.

CAn you point me in the right direction.

Thanks,

Scott

 

[Arvin Meyer MVP]

You need to add a query column that strips out the extraneous characters and
search on that, or build a search form that changes the search value from 10
numbers to the value you want to search for, so the form would need 2 text
boxes, 1 visible and 1 hidden. In the after update event of the visible
textbox use some code like (untested):

Sub VisibleBox_AfterUpdate()

Me.HiddenBox = "(" & Left([VisibleBox], 3) & " " & Mid([VisibleBox],4,3)
& " " & Right([VisibleBox],4)

End Sub

In your query criteria box use:

[Forms]![FormName]![HiddenBox]

of course you need to substitute all your names.

 

[Armen Stein]

Sub VisibleBox_AfterUpdate()

Me.HiddenBox = "(" & Left([VisibleBox], 3) & " " & Mid([VisibleBox],4,3)
& " " & Right([VisibleBox],4)

End Sub

Or, if you're not sure how the phone number will be formatted, you
could use a little function like this to strip out the special
characters (no error handling shown):

---------------------
Public Function StrippedPhoneNumber(FormattedPhoneNumber As Variant) _
As Variant
Dim strPhoneNumber As String

If IsNull(FormattedPhoneNumber) Then
StrippedPhoneNumber = Null
Else
strPhoneNumber = FormattedPhoneNumber
strPhoneNumber = Replace(strPhoneNumber, "(", "")
strPhoneNumber = Replace(strPhoneNumber, ")", "")
strPhoneNumber = Replace(strPhoneNumber, "-", "")
strPhoneNumber = Replace(strPhoneNumber, " ", "")
strPhoneNumber = Replace(strPhoneNumber, ".", "")
StrippedPhoneNumber = strPhoneNumber
End If

End Function

---------------------

Note: If your phone numbers use other formats, like extensions, you
would need to modify this code to handle them.

Then, your form code could be:

Me.HiddenBox = StrippedPhoneNumber(Me.VisibleBox)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com