By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,125 Members | 548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,125 IT Pros & Developers. It's quick & easy.

Evaluate and compare phone numbers for time zone.

P: 78
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?

Apr 5 '10 #1
Share this Question
Share on Google+
3 Replies

P: 78

I tried the following in the query:

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. \(999") "000\-0000" x"999999;;_
So as an example, say I have the following three numbers when I run the query:


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???
Apr 5 '10 #2

P: 78
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:

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)?

Apr 5 '10 #3

P: 78
I keep seeing that if the "text" part of
Expand|Select|Wrap|Line Numbers
  1. 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?

Apr 7 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.