Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question 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.

    D.

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    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

    [Forms]![2ProspectT]![RingT5]+(0.0208333333*6)

    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.

    Mike

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    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

    regards

    Gareth

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You can use API calls to get the UTC, which is the same as the old GMT. try having a look at AllApi.net

    Regards

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    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",
    or
    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?

    Thanks,
    D.

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    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.

    If/when i get the time i will have a go at it...

    Regards

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I did a quick search for you...
    Code:
    Option Compare Database
    Option Explicit
    
    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
    End Type
    
    
    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
    End Type
    
    Public Function GetUTCOffset() As Date
        Dim lngRet As Long
        Dim udtTZI As TimeZoneInfo
        
        lngRet = GetTimeZoneInformation(udtTZI)
        GetUTCOffset = udtTZI.lngBias / 60 / 24
    End Function
    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

    Regards & GL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •