Show me a contact's local time?

[dniezby]

Anyone have any idea if there is a way for me to put a live field on a form that shows me my contact's local time?

Imagine a contact form.

I want to call that contact, in the top corner of the form is says - CTX Local Time: 9:40pm

Opps, don't want to call this late.


Hope it could be done. This would be awesome to have on any contact form application.

Thanks.

[Bob Fitz]

You will need a field in your contacts table in which you can put the number of hours they are ahead or behind your own time. Then on your form you would just need a text box with an expression as its Row Source. The expression would use the DateAdd() function and the stored data of hours difference to do the calculation.

[dniezby]

You will need a field in your contacts table in which you can put the number of hours they are ahead or behind your own time. Then on your form you would just need a text box with an expression as its Row Source. The expression would use the DateAdd() function and the stored data of hours difference to do the calculation.

Well, I do have a time zone field on the form. It tells me -6, -5 etc. I'm not familiar with DateAdd


Sent from my iPhone using Tapatalk

[Bob Fitz]

For info on DateAdd() search google or Access Help. For example see: https://www.techonthenet.com/access/...te/dateadd.php

Expression will be something like: =DateAdd("h",[name of your time zone field],Now())

[dniezby]

For info on DateAdd() search google or Access Help. For example see: https://www.techonthenet.com/access/...te/dateadd.php

Expression will be something like: =DateAdd("h",[name of your time zone field],Now())

Thanks, this helps a lot.

So I would put this in a new field or could I use a label?

[dniezby]

For info on DateAdd() search google or Access Help. For example see: https://www.techonthenet.com/access/...te/dateadd.php

Expression will be something like: =DateAdd("h",[name of your time zone field],Now())

Okay, this - sort of - worked.

This is what I used :

=DateAdd("h",[Timezone],Now())

Works great - HOWEVER, it's calculating the wrong column.

My timezone field uses a lookup from the timezone table. That table is

ID
Name (Common name (IE: Central Standard Time) )
Offset (GMT Offset (IE: -6) )
Code (Name abbv (IE: CST) )

In the lookup I show two columns ( Name & Offset ) but I only save the ID

Using the above mentioned method, it appears to be calculating on the record ID when I need it to calculate on the offset.

Is there a way to target it to that column OR should I modify the timezone table by getting rid of the ID column and using the Offset column as a primary key and reference?

[Bob Fitz]

You do not have fields on a form. You have "Controls" that are bound to fields of a table or query. These controls display the data in the query/table. Use an unbound text box and put the expression as the "Control Source" setting of this text box.
I would set its Enabled property to NO and its Locked property to Yes.

[Bob Fitz]

Okay, this - sort of - worked.

This is what I used :

=DateAdd("h",[Timezone],Now())

Works great - HOWEVER, it's calculating the wrong column.

My timezone field uses a lookup from the timezone table. That table is

ID
Name (Common name (IE: Central Standard Time) )
Offset (GMT Offset (IE: -6) )
Code (Name abbv (IE: CST) )

In the lookup I show two columns ( Name & Offset ) but I only save the ID

Using the above mentioned method, it appears to be calculating on the record ID when I need it to calculate on the offset.

Is there a way to target it to that column OR should I modify the timezone table by getting rid of the ID column and using the Offset column as a primary key and reference?

I assume that [Timezone] is a combo box. If so, try:
=DateAdd("h",[Timezone].column(2),Now())

[dniezby]

I assume that [Timezone] is a combo box. If so, try:
=DateAdd("h",[Timezone].column(2),Now())

That worked-ish.

It's giving me the time related to my local time but it won't be accurate if the user moves around. I also have to change the way I store the time offset. Right now I'm storing it as an offset to GMT. For example, Central Standard Time is -6...

I wonder is I could do something like this?

=DateAdd("h",[Timezone].Column(2)+6,Now())


Testing it seems to work fine for timezones that are single digit offsets. So, Eastern time = -5 but when I select Hawaii timezone ( -10 ) it's an hour off to the later. It says it's 6am when it's really 5am. Also, I don' t think the +6 part would work if my user was on the East coast or west.

So, I guess I have to figure out how to return the GMT time first then do the math so that it's accurate no matter where my user is using the application.

[dniezby]

Okay so here is what I'm thinking.

I create a new table called "Settings" - In the settings, I have a look up to the timezone table. This way the user could change his / her current timezone through the settings.

Then I create a Form onLoad event.

Private Sub Form_Load()
Dim TimeSetting as String
TimeSetting = DLookup("[timezone]", "settings")
End Sub

Then in the "local time" control box I use that formula

=DateAdd ("h",[Timezone].Column(2)+TimeSetting,Now())

Would that work or did I totally screw up my thinking?

[orange]

It seems to me that as long as the user "moves around" you're chasing your tail.
The user would have to identify where he/she is in order for the "local time" to be correct.

If user is in UK today and Canada tomorrow, you need something to identify where the user is at the time to show local time consistently.

Now, for a quick diversion, if you had a function to "talk" to googleMaps or Earth or some related product, you might be able to determine where the user is (Lat/long or whatever), then use that to identify "Local" timezone.
User could type in City/country or select from a list....
Good luck.

[HiTechCoach]

I have an example I created to work with time zones that might be helpful:

Time Zones (Click Here)

[dniezby]

Yeah that's way more intense than I was looking to do. I'm just gonna create. A settings table and set their time zone from there.


Sent from my iPhone using Tapatalk

[orange]

Very nice Boyd. Good to know - great reference.
I think that would qualify as a

if you had a function to "talk" to googleMaps or Earth or some related product

[HiTechCoach]

Very nice Boyd. Good to know - great reference.

Thank you. Glad it was helpful.

I think that would qualify as a

"if you had a function to "talk" to googleMaps or Earth or some related product"

Interesting. Some way to detect a time zone based on information about who you want to contact. There may be a web services that can be called to lookup the time zone by ZIP or Area Code.

This could be useful for come of my customers. I will have to check into it.

I currently use a web service to get the atomic clock date/time GMT. I use the time zone offset to get the "local" time offset. I use it to check the PC's system clock to see it it is correct.