Unanswered: now() with users using different time zones?
On the same physical location/office, I have users setting some of the computers to different time zones for several reasons related to their clients etc.
Sometimes I want to set a shared database to set the timestamp Now() automatically whenever a change happens.
Now, how do I best approach this when I want the correct local time, and not what happens to be used on the actual PC from which the database happens to be accessed?
I guess this involves some API/networking tricks to get the time from a particular machine or something similar. Code examples, anyone?
Out of curiosity I would like to know how to get a time through the web/internet into access as well (examples), although that is not an option for the db in question just now.
I don't know if this is of any help but I have a telemarketing data base and we make appointments for agent across Australia where we have up to a 3 hour time difference.
This is the expression I use in a SetValue macro action when the agent who the appointment is being made for is in Perth and we are in Sydney
Perth is 3 hours behind Sydney in daylight saving. If the prospect being called in Perth says "could you phone me back next Tuesday at 2pm, then when we click 2 pm on the drop down list the time is changed to 5pm.
To make that give a Now type entry then you could join the time and date field.
Looking at what Mike has done seems to be the start of the best option for you. As a thought what about using the timezone from the inputting machine as you already have then, setting another field that will be populated with the time difference for example: UK 0800 GMT in the Timezone then in the Difference field will be New Yorks of -5 (this is the hours you are infront or behind the timezone) that way when calculated you can run a query that will shown Timezone + Difference would then show 3 am
This also allows for the Clocks moving forward or back in the various countries if thats is also something you have to deal with
I hope that ive made this clear enough to get your head around?
If not shout and Ill see if i can do a better job of it
I am not a hard core programmer, not sure if I know all aspects of using Access' API possibilities.
Anyway, I need only to call a function/library or other that either can be told that on THIS workstation (1), return time + x hours, on THIS workstation (2) return time without adding/subtracting anything, and on THIS workstation (3) subtract x hours and return the resulting time as "local time",
using API, checking what time zone windows is set up to use, then automatically calculating the eqivalent time for the actual local time zone where the office is physically located.
So, I either need to get the name of the workstation (not a water-proof solution), or the time zone.
If I should do a time zone calculation, I would need to know exactly how all the different time zone names are stored in windows, and include them for the calculation in my code, I guess.
Anyone who has that list, and can give me a code example to use while retrieving the time zone name for the actual computer?
I dont have time for it right now, but i have done time zones (UTC) before. It doesnt return "timezone names", i think it simply returned your offset to UTC. Thus if you store UTC and get your UTC offset you can allways get the time you need.
Option Compare Database
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TimeZoneInfo) As Long
Private Type SystemTime
intYear As Integer
intMonth As Integer
intwDayOfWeek As Integer
intDay As Integer
intHour As Integer
intMinute As Integer
intSecond As Integer
intMilliseconds As Integer
Private Type TimeZoneInfo
lngBias As Long
intStandardName(32) As Integer
intStandardDate As SystemTime
intStandardBias As Long
intDaylightName(32) As Integer
intDaylightDate As SystemTime
intDaylightBias As Long
Public Function GetUTCOffset() As Date
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
lngRet = GetTimeZoneInformation(udtTZI)
GetUTCOffset = udtTZI.lngBias / 60 / 24
Paste above code into a module then you can get the UTC time by: Now() + GetUTCOffset
To go from UTC Time to Local use the oposite: [Stored UTC] - GetUTCOffset