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.
