Timezone Calculation

[Melanie]

I need a calculation that will extract the timezone of each record. I have
area codes for each record and I need something that will look at that area
code and let me know whether it is EST, CST, etc. Can someone please help?
Thanks

 

[Mark Andrews]

I assume you need a table with area code and timezone and you just do a
simple DLookup or join this table into your query to retrieve the timezone.

I did a few quick searches (about 10 minutes) and didn't find any free
tables, here's some if you don't mind some typing:
http://newbrokertraining.tripod.com/sitebuildercontent/sitebuilderfiles/timezonechart.pdf

http://area-codes.1keydata.com/

I know there are some available for $50 or so.

Maybe someone else knows of a table you can get with the cross reference?

The only other route I could think of is calling a webservice (but I doubt
you want to go down that route).

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[John W. Vinson]

I need a calculation that will extract the timezone of each record. I have
area codes for each record and I need something that will look at that area
code and let me know whether it is EST, CST, etc. Can someone please help?
Thanks

No "calculation" is possible, since there is no functional or arithmetic
relationship between these values.

You will need a table of the timezone for each area code, and then a very
simple join query or DLookUp function call. I see that Mark has given you a
couple of possible links.

Do note that there are area codes which span timezone boundaries. All of
Idaho is area code 208; parts of Idaho are in Mountain and other parts are in
Pacific time. In short I don't think that the area code provides sufficient
information.

 

[Mark Andrews]

Perhaps zip code to time zone would work better?
Not sure if you have address information as well as phone number?
I think this one is $40:
http://www.zipcodeworld.com/zipcodepremium.htm

I think I'm out of ideas,
Mark

 

[Melanie]

Thanks for your help. I am not familiar with the Dlookup function. If I
created a table with the area codes and timezones. What would be the
expression I have to enter in the query?

I know sometimes the area code is not the best way to do things but it does
not need to be perfect.

 

[John W. Vinson]

Perhaps zip code to time zone would work better?

It's certainly finer-grained and would have a better chance, and fewer
exceptions; but still a table driven approach would be needed. A "calculation"
is a non-starter.

 

[John W. Vinson]

Thanks for your help. I am not familiar with the Dlookup function. If I
created a table with the area codes and timezones. What would be the
expression I have to enter in the query?

I know sometimes the area code is not the best way to do things but it does
not need to be perfect.

I'd use one of two options:

1. In your Query, Join the timezone table to the table containing area codes,
by area code. Since I cannot see the structure of either table from my current
vantage point I don't know how to do so.

2. On your Form or Report, use

=DLookUp("[timezone]", "tablename]", "[areacode] = '" & Left([Phone], 3) &
"'")

assuming that you have a field Phone for which the first three characters are
the area code.

Since, again, you haven't posted any information about your tables, this is
just a place to get started.