Evaluate and compare phone numbers for time zone.

[WannabePrgmr]

I have a form in access 2003 that is for input only. Customers names and phone numbers are entered by users. I am trying to help the users by identifying what time zone a given phone number is in. The only way I can think of is to somehow sort/copy the 1st three numbers of each phone number (the area code) and compare it to all the area codes I would have listed in a table with the time zone listed with each.

What I am visioning is that when a user runs a query, the query will have a list of phone numbers in one column and the time zone (Eastern, Pacific etc) next to each.

Any ideas?

Thanks!

[WannabePrgmr]

Update:

I tried the following in the query:

AreaCode: format([Phone Number], left([Phone Number], 3))

When I run the query, it seems to work for the first number, but the rest of them show the 1st two numbers and then the entire 9 digit number after it!

I have the Phone Number field formatted for

\(999") "000\-0000" x"999999;;_

So as an example, say I have the following three numbers when I run the query:

5623936554
9896237889
9126589969

The AreaCode field returns the following:

562 (which is perfect)
989896237889 (it copies the first two and then the entire number)
919126589969 (same as above).

I'm not sure why???

[WannabePrgmr]

I don't mean to keep answering my own questions, but after taking a close look, it only does the above when the 1st three digits contain a "0".

So a more accurate example is:

2709932356
will return: 272709932356

But 2719932356
will return: 271

How do I make it treat 0's as good numbers (or at least not care that it's a zero)?

Thanks

[WannabePrgmr]

I keep seeing that if the "text" part of

left(text, #)

is "" or zero, that it will return "text", which is what it is doing. It's actually starting to return the first 3 characters on the left, until it see's a "0" and then it returns the entire number ("text").

I have this field set as a text field but apparently that isn't good enough.

Does anyone know how to code it so that it will see a "0" as it would any letter, or other number?

Thanks!