 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2007Time Zone change
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 Sorry I should have said "Daylight Savings Time" change - not Time Zone. > Hi - I have an Access 2003 database that calculates time duration for > products that are checked-out and checked-in. Everything works just [quoted text clipped - 13 lines] > Thanks > LeAnn 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.) >Sorry I should have said "Daylight Savings Time" change - not Time Zone. > [quoted text clipped - 15 lines] >> Thanks >> LeAnn -- John Nurick [Microsoft Access MVP] Please respond in the newsgroup and not by email. 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. > Hi LeAnn, > [quoted text clipped - 42 lines] > > Please respond in the newsgroup and not by email. 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? > Hi LeAnn, > [quoted text clipped - 42 lines] > > Please respond in the newsgroup and not by email. 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? >This works great John - however there is one slight problem. When the >system time zone is Arizona, I get "Cannot determine current time zone." [quoted text clipped - 49 lines] >> >> Please respond in the newsgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newsgroup and not by email. 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? > When I set my computers time zone to Arizona, the VBNet > GetCurrentTimeZone() function returns "Cannot determine current time [quoted text clipped - 63 lines] > > Please respond in the newsgroup and not by email. 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. >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 [quoted text clipped - 71 lines] >> >> Please respond in the newsgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newsgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.