Time Zone change

[LeAnn]

Hi - I have an Access 2003 database that calculates time duration for
products that are checked-out and checked-in. Everything works just
perfectly but then this is used in Arizona so I didn’t need to consider any
time zone changes. Now they want to use the database in Texas. Can anyone
give me some tips as to how I should approach calculating the duration of a
product that is checked out when the time zone changes? Basically I have a
table that contains checkout and checkin time fields and user defined
functions that calculate duration and alerts as needed (these products expire
after a certain amount of time has elapsed). Would I simply add (or
subtract) an hour to the check in time if checked out before 2:00 AM on the
dates of change? I would also need a lookup table with all of the dates that
the time changes - correct?

I hope I’m making enough sense………

Thanks
LeAnn

 

[LeAnn]

Sorry I should have said "Daylight Savings Time" change - not Time Zone.

 

[John Nurick]

Hi LeAnn,

You don't need a table with daylight savings dates: there's code here
http://vbnet.mvps.org/index.html?code/locale/gettimezonebias.htm that
can get the current time zone and daylight savings information from
Windows. It's classic VB code that will need little if any modification
to work in VBA. As far as I know you'll get different answers at 01:59
and 02:01 on the day of the change.

So one way to go would be to store the check-in and check-out times in
standard time (i.e. if DS is in force, subtract an hour from the entered
value before storing it). Or if the database may in future be used
across multiple time zones, convert the time to UTC and store that.
Either way, calculating the duration would be a simple subtraction.

If you need to be able to retrieve the actual "clock" time of the
various events, you could store that and add a field to store the
daylight saving "bias" at the time of each event. (Alternatively,
http://vbnet.mvps.org/index.html?code/locale/gettimezonechange.htm gets
the dates of the change - though it may have been affected by this
year's change in US DS dates.)

 

[LeAnn]

Thanks for your response, John. Your suggestion matches with what I kind of
worked out after my post. I do save the clock times of the events and added
a field for each event - CkOutZone and CkInZone (for example) to store the
time zone for each. I checked out the references you gave but there was no
code on either page under "Add the following code". I'll check there again
later. I think I found some similar code and am working to modify it for my
needs.

 

[LeAnn]

This works great John - however there is one slight problem. When the
system time zone is Arizona, I get "Cannot determine current time zone."
This makes it hard for me - having users in AZ, to deal with error handling
if there was a glitch for a TX user. The only way I see to deal with that is
to create an initial startup form where the user clicks their location so I
can set a global variable. Do you agree?

 

[John Nurick]

When I set my computers time zone to Arizona, the VBNet
GetCurrentTimeZone() function returns "Cannot determine current time
zone" for me too. A quick web search doesn't find any other reports of
this. Conceivably it's a bug in the patch issued to update Windows XP
for the recent daylight saving changes.

But surely you don't need the name of the time zone, just the current
bias?

 

[LeAnn]

I'm using the name of the time zone because I'm not sure I understand the
bias very well. I think I can manipulate the times based on time zone name
conditions.

Also, since I've been trying to use the API code for this, I've been having
problems with my PC. I'm getting "There isn't enough memory......." errors.
Is there some memory management issues with using API calls?

 

[John Nurick]

Hi Leanne,

If you've copied the code from
http://vbnet.mvps.org/index.html?code/locale/gettimezoneinfo.htm you can
just do this to find out whether the computer reckons daylight saving is
in force:

Function IsDaylightSavingTime() As Boolean
Dim tzi As TIME_ZONE_INFORMATION

If GetTimeZoneInformation(tzi) = TIME_ZONE_ID_DAYLIGHT Then
IsDaylightSavingTime = True
Else
IsDaylightSavingTime = False
End If

End Function

There are no memory management issues with API calls as such.
Mis-handling some API functions can screw things up quite well, but I
can't see anything in the code at
http://vbnet.mvps.org/index.html?code/locale/gettimezoneinfo.htm that
might cause any problems.