Formatting when working with phone numbers in queries

F

faxylady

In a previous post, Klatuu suggested it is not a good idea to format phone
numbers in tables when you are going to use them in queries. I was not aware
of this and would like to know the best way to format numbers when they will
be used in queries. How do I remove formatting and input masks that are
already in my tables? Most of my phone number formatting is in the form
(999) 999-9999. I retrieve this contact information from various websites
where they are already formatted. Thank you.
 
W

Wayne-I-M

Hi

This may be wrong but when I was learning access I was always told that
anything that can be added or divided was a number
i.e
1+2=3
10 / 2 =5
and everything else wasn't.

i.e.
"one + "two" = Error
"ten" divided by "five = Error


So it really depends on what your want to do with the data (in this case
phone numbers). If you want to use them in numerical formulas then they are
numbers if not tey aren't and so they are text.

Your input mask of (999) 999-9999
could be changed to (aaa) aaa-aaaa for text
or
(CCC) CCC-CCCC which would allow both numbers or text
all of these make the input "optional" - just in case you don't have a tel
number

Hope this was helpful
 
F

faxylady

Somehow, our teaching may have been different. I was taught that fields
such as zip codes, phone numbers, Soc security numbers, etc should always be
text files. At any rate, how do I change these text files that are already
formatted?
 
J

John Spencer

Well, I disagree with Klatuu to some extent on storing formatted numbers. I
tend to store formatting with phone numbers. And by the way, I agree with
you - if you aren't going to do math on the number, it is not a number - it
is a string with numeric characters.

Some Reasons to retain formatting ( and not use an input mask)
--Not all countries format phone numbers the same (they may not even have
the same number of digits).
--Easier to generate reports, display on screen etc.

Some Reasons to store only the digits
-- All phone numbers are consistent in form (all from one country)
-- Easier to search since you don't need to worry about formatting
characters
-- Easier to change the way the numbers are formatted when displaying (when
consistent format is needed)


That said, I have a routine that checks entries to see if they have exactly
ten numeric characters and if they do, it will strip out all extraneous
characters and then reformat the number into a specific US format.

Whether you store the format or not, depends on the use you are going to
make of the data and the consistency of the data itself.
 
F

faxylady

Regarding Phone numbers being formatted differently (eg. two parenthesis and
dash or slash after area code and dash), after several hours work and advice
from this site, I have put together criteria for queries to identify phone
numbers regardless of formatting. It seemed to work with what I am doing,
Would like to know what you think.

In Criteria Row, under Phone field, write Left ([phone],4) or like (“?999*â€)
or like (“999*â€) or like (“?222*â€) or like (“222*â€) or like (“?555*â€) or like
(“555*"). [IN] should be substituted for the first [or like] without
brackets. Or Like was automatically substituted for IN by Access when run
was clicked.

What I am doing here is identifying phone numbers for use in other queries.
Let me know what you think.
 
J

John Spencer

Not quite sure what you have done. I think you would only need to use

Left(Phone,3) in ("999","222","555") Or Mid(Phone,2,3) In
("999","222","555")

Field: Left(Phone,3)
Criteria(1): in ("999","222","555")

Field:Mid(Phone,2,3)
Criteria(2):In ("999","222","555")

Since you need to search on Area Code, it might be a good idea to break out
the area code into a separate field. OR you need to ensure consistently
formatted phone numbers.

You could run an update query to get the consistent formatting. The
function is included in this posting.

UPDATE yourTable
SET YourTable.Phone = Format( NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

WARNING: Backup your data before you do this. If something goes wrong, you
cannot UNDO.

Alternatives:
Add an area code field to the table and poplulate it with
UPDATE yourTable
SET YourTable.AreaCode = Left( NumPart([YourTable].[Phone]),3)
WHERE YourTable.Phone is Not Null

OR
Add a new field "formattedphone" to your table and populate it with

UPDATE yourTable
SET YourTable.FormattedPhone = Format( NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

If the results are good, you could then delete Phone field and rename
FormattedPhone to "Phone"

You would need the following function saved in a vba module

'=========== VBA Code Follows =============
Public Function NumPart(strIn As String) As String
'doesn't handle nulls
'or other data types

Dim iPos As Integer
Dim strCh As String

NumPart = ""
For iPos = 1 To Len(strIn)
strCh = Mid(strIn, iPos, 1)
If IsNumeric(strCh) Then
NumPart = NumPart & strCh
End If
Next iPos

End Function

faxylady said:
Regarding Phone numbers being formatted differently (eg. two parenthesis
and
dash or slash after area code and dash), after several hours work and
advice
from this site, I have put together criteria for queries to identify phone
numbers regardless of formatting. It seemed to work with what I am doing,
Would like to know what you think.

In Criteria Row, under Phone field, write Left ([phone],4) or like
("?999*")
or like ("999*") or like ("?222*") or like ("222*") or like ("?555*") or
like
("555*"). [IN] should be substituted for the first [or like] without
brackets. Or Like was automatically substituted for IN by Access when run
was clicked.

What I am doing here is identifying phone numbers for use in other
queries.
Let me know what you think.



John Spencer said:
Well, I disagree with Klatuu to some extent on storing formatted numbers.
I
tend to store formatting with phone numbers. And by the way, I agree
with
you - if you aren't going to do math on the number, it is not a number -
it
is a string with numeric characters.

Some Reasons to retain formatting ( and not use an input mask)
--Not all countries format phone numbers the same (they may not even have
the same number of digits).
--Easier to generate reports, display on screen etc.

Some Reasons to store only the digits
-- All phone numbers are consistent in form (all from one country)
-- Easier to search since you don't need to worry about formatting
characters
-- Easier to change the way the numbers are formatted when displaying
(when
consistent format is needed)


That said, I have a routine that checks entries to see if they have
exactly
ten numeric characters and if they do, it will strip out all extraneous
characters and then reformat the number into a specific US format.

Whether you store the format or not, depends on the use you are going to
make of the data and the consistency of the data itself.
 
F

faxylady

Thank you. Your last suggestion sounds most plausible, but I will have to
study it. Keep in mind these Contact numbers are acquired from sources over
which I have no control, ex. web sites with contact information. How they
format the information is how I have to accept it, unless you have further
suggestions as to how to reformat so the info is more facile to Access.

John Spencer said:
Not quite sure what you have done. I think you would only need to use

Left(Phone,3) in ("999","222","555") Or Mid(Phone,2,3) In
("999","222","555")

Field: Left(Phone,3)
Criteria(1): in ("999","222","555")

Field:Mid(Phone,2,3)
Criteria(2):In ("999","222","555")

Since you need to search on Area Code, it might be a good idea to break out
the area code into a separate field. OR you need to ensure consistently
formatted phone numbers.

You could run an update query to get the consistent formatting. The
function is included in this posting.

UPDATE yourTable
SET YourTable.Phone = Format( NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

WARNING: Backup your data before you do this. If something goes wrong, you
cannot UNDO.

Alternatives:
Add an area code field to the table and poplulate it with
UPDATE yourTable
SET YourTable.AreaCode = Left( NumPart([YourTable].[Phone]),3)
WHERE YourTable.Phone is Not Null

OR
Add a new field "formattedphone" to your table and populate it with

UPDATE yourTable
SET YourTable.FormattedPhone = Format( NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

If the results are good, you could then delete Phone field and rename
FormattedPhone to "Phone"

You would need the following function saved in a vba module

'=========== VBA Code Follows =============
Public Function NumPart(strIn As String) As String
'doesn't handle nulls
'or other data types

Dim iPos As Integer
Dim strCh As String

NumPart = ""
For iPos = 1 To Len(strIn)
strCh = Mid(strIn, iPos, 1)
If IsNumeric(strCh) Then
NumPart = NumPart & strCh
End If
Next iPos

End Function

faxylady said:
Regarding Phone numbers being formatted differently (eg. two parenthesis
and
dash or slash after area code and dash), after several hours work and
advice
from this site, I have put together criteria for queries to identify phone
numbers regardless of formatting. It seemed to work with what I am doing,
Would like to know what you think.

In Criteria Row, under Phone field, write Left ([phone],4) or like
("?999*")
or like ("999*") or like ("?222*") or like ("222*") or like ("?555*") or
like
("555*"). [IN] should be substituted for the first [or like] without
brackets. Or Like was automatically substituted for IN by Access when run
was clicked.

What I am doing here is identifying phone numbers for use in other
queries.
Let me know what you think.



John Spencer said:
Well, I disagree with Klatuu to some extent on storing formatted numbers.
I
tend to store formatting with phone numbers. And by the way, I agree
with
you - if you aren't going to do math on the number, it is not a number -
it
is a string with numeric characters.

Some Reasons to retain formatting ( and not use an input mask)
--Not all countries format phone numbers the same (they may not even have
the same number of digits).
--Easier to generate reports, display on screen etc.

Some Reasons to store only the digits
-- All phone numbers are consistent in form (all from one country)
-- Easier to search since you don't need to worry about formatting
characters
-- Easier to change the way the numbers are formatted when displaying
(when
consistent format is needed)


That said, I have a routine that checks entries to see if they have
exactly
ten numeric characters and if they do, it will strip out all extraneous
characters and then reformat the number into a specific US format.

Whether you store the format or not, depends on the use you are going to
make of the data and the consistency of the data itself.


Somehow, our teaching may have been different. I was taught that
fields
such as zip codes, phone numbers, Soc security numbers, etc should
always
be
text files. At any rate, how do I change these text files that are
already
formatted?
:

Hi

This may be wrong but when I was learning access I was always told
that
anything that can be added or divided was a number
i.e
1+2=3
10 / 2 =5
and everything else wasn't.

i.e.
"one + "two" = Error
"ten" divided by "five = Error


So it really depends on what your want to do with the data (in this
case
phone numbers). If you want to use them in numerical formulas then
they
are
numbers if not tey aren't and so they are text.

Your input mask of (999) 999-9999
could be changed to (aaa) aaa-aaaa for text
or
(CCC) CCC-CCCC which would allow both numbers or text
all of these make the input "optional" - just in case you don't have a
tel
number

Hope this was helpful

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

In a previous post, Klatuu suggested it is not a good idea to format
phone
numbers in tables when you are going to use them in queries. I was
not
aware
of this and would like to know the best way to format numbers when
they
will
be used in queries. How do I remove formatting and input masks that
are
already in my tables? Most of my phone number formatting is in the
form
(999) 999-9999. I retrieve this contact information from various
websites
where they are already formatted. Thank you.
 
J

John Spencer

One final suggestion, if you are doing this a lot (which it sounds as if you
may be doing) and you decide to use the UPDATE Query routine you might try a
modification to the update query so it will only update the records where
phone is not in the specified format.

UPDATE yourTable
SET YourTable.Phone =
Format( NumPart([YourTable].[Phone]),"(@@@) @@@\-@@@@")
WHERE [Phone] NOT LIKE "(???) ???-????"

faxylady said:
Thank you. Your last suggestion sounds most plausible, but I will have to
study it. Keep in mind these Contact numbers are acquired from sources
over
which I have no control, ex. web sites with contact information. How they
format the information is how I have to accept it, unless you have further
suggestions as to how to reformat so the info is more facile to Access.

John Spencer said:
Not quite sure what you have done. I think you would only need to use

Left(Phone,3) in ("999","222","555") Or Mid(Phone,2,3) In
("999","222","555")

Field: Left(Phone,3)
Criteria(1): in ("999","222","555")

Field:Mid(Phone,2,3)
Criteria(2):In ("999","222","555")

Since you need to search on Area Code, it might be a good idea to break
out
the area code into a separate field. OR you need to ensure consistently
formatted phone numbers.

You could run an update query to get the consistent formatting. The
function is included in this posting.

UPDATE yourTable
SET YourTable.Phone = Format( NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

WARNING: Backup your data before you do this. If something goes wrong,
you
cannot UNDO.

Alternatives:
Add an area code field to the table and poplulate it with
UPDATE yourTable
SET YourTable.AreaCode = Left( NumPart([YourTable].[Phone]),3)
WHERE YourTable.Phone is Not Null

OR
Add a new field "formattedphone" to your table and populate it with

UPDATE yourTable
SET YourTable.FormattedPhone = Format(
NumPart([YourTable].[Phone]),"(@@@)
@@@\-@@@@")
WHERE Phone is Not Null

If the results are good, you could then delete Phone field and rename
FormattedPhone to "Phone"

You would need the following function saved in a vba module

'=========== VBA Code Follows =============
Public Function NumPart(strIn As String) As String
'doesn't handle nulls
'or other data types

Dim iPos As Integer
Dim strCh As String

NumPart = ""
For iPos = 1 To Len(strIn)
strCh = Mid(strIn, iPos, 1)
If IsNumeric(strCh) Then
NumPart = NumPart & strCh
End If
Next iPos

End Function

faxylady said:
Regarding Phone numbers being formatted differently (eg. two
parenthesis
and
dash or slash after area code and dash), after several hours work and
advice
from this site, I have put together criteria for queries to identify
phone
numbers regardless of formatting. It seemed to work with what I am
doing,
Would like to know what you think.

In Criteria Row, under Phone field, write Left ([phone],4) or like
("?999*")
or like ("999*") or like ("?222*") or like ("222*") or like ("?555*")
or
like
("555*"). [IN] should be substituted for the first [or like] without
brackets. Or Like was automatically substituted for IN by Access when
run
was clicked.

What I am doing here is identifying phone numbers for use in other
queries.
Let me know what you think.



:

Well, I disagree with Klatuu to some extent on storing formatted
numbers.
I
tend to store formatting with phone numbers. And by the way, I agree
with
you - if you aren't going to do math on the number, it is not a
number -
it
is a string with numeric characters.

Some Reasons to retain formatting ( and not use an input mask)
--Not all countries format phone numbers the same (they may not even
have
the same number of digits).
--Easier to generate reports, display on screen etc.

Some Reasons to store only the digits
-- All phone numbers are consistent in form (all from one country)
-- Easier to search since you don't need to worry about formatting
characters
-- Easier to change the way the numbers are formatted when displaying
(when
consistent format is needed)


That said, I have a routine that checks entries to see if they have
exactly
ten numeric characters and if they do, it will strip out all
extraneous
characters and then reformat the number into a specific US format.

Whether you store the format or not, depends on the use you are going
to
make of the data and the consistency of the data itself.


Somehow, our teaching may have been different. I was taught that
fields
such as zip codes, phone numbers, Soc security numbers, etc should
always
be
text files. At any rate, how do I change these text files that are
already
formatted?
:

Hi

This may be wrong but when I was learning access I was always told
that
anything that can be added or divided was a number
i.e
1+2=3
10 / 2 =5
and everything else wasn't.

i.e.
"one + "two" = Error
"ten" divided by "five = Error


So it really depends on what your want to do with the data (in this
case
phone numbers). If you want to use them in numerical formulas then
they
are
numbers if not tey aren't and so they are text.

Your input mask of (999) 999-9999
could be changed to (aaa) aaa-aaaa for text
or
(CCC) CCC-CCCC which would allow both numbers or text
all of these make the input "optional" - just in case you don't
have a
tel
number

Hope this was helpful

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

In a previous post, Klatuu suggested it is not a good idea to
format
phone
numbers in tables when you are going to use them in queries. I
was
not
aware
of this and would like to know the best way to format numbers
when
they
will
be used in queries. How do I remove formatting and input masks
that
are
already in my tables? Most of my phone number formatting is in
the
form
(999) 999-9999. I retrieve this contact information from various
websites
where they are already formatted. Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top