Help with Cross Time Zone calculations

  • Thread starter Thread starter Rico
  • Start date Start date
R

Rico

Hello,

I have a situation where I have an SQL Server AND a remote MS Access
application sitting in one timezone, while the people dialing in (via remote
desktop) to do the work are in another time zone. The timezone where the
the server and application reside observes daylight savings time, while the
timezone where the user resides does not observe daylight savings time. If
daylight savings time was observed in both places, then I could simply
adjust the current time by two hours to make them equal, but that won't work
since one zone doesn't observe daylight savings.

Is there any way I can see if daylight savings is in effect to determine
whether I should adjust 1 or two hours? or if anyone has a better way of
handling this please let me know.

Thanks!
Rick
 
Rico said:
Hello,

I have a situation where I have an SQL Server AND a remote MS Access
application sitting in one timezone, while the people dialing in (via
remote desktop) to do the work are in another time zone. The timezone
where the the server and application reside observes daylight savings
time, while the timezone where the user resides does not observe daylight
savings time. If daylight savings time was observed in both places, then
I could simply adjust the current time by two hours to make them equal,
but that won't work since one zone doesn't observe daylight savings.

Is there any way I can see if daylight savings is in effect to determine
whether I should adjust 1 or two hours? or if anyone has a better way of
handling this please let me know.

Thanks!
Rick
Click to expand...
 
Rick:

The simplest way would be to store the start and end dates of the daylight
saving time periods in two columns in a table. You can then look up the
current date value and adjust the current date/time value accordingly, e.g.

Function AdjustCurrentTime() As Date

Dim strCriteria As String

AdjustCurrentTime = DateAdd("h", 1, VBA.Now())

strCriteria = "#" & Format(VBA.Date, "yyyy-mm-dd") & "# " & _
"Between DSTStarts And DSTEnds"

If Not IsNull(DLookup("DSTStarts", "DSTTimes", strCriteria)) Then
AdjustCurrentTime = DateAdd("h", 2, VBA.Now())
End If

End Function

Just call the function to get the adjusted current time.

The above function handles your specific requirement only. For a more
flexible solution you could add columns such as TimeZones Adjustment (the
number of hours of adjustment as a positive or negative number) and
ObservesDST to the DSTTimes table and pass the TimeZone value into the
function, amending the code to then look up the adjustment for the time zone
in question.

I've not allowed for the period between midnight and 2.00 AM on the days
when DST starts and ends, but it would be a simple task to amend the function
and include 2:00 AM in the values in the DSTTimes table if you wanted it
precise.

Ken Sheridan
Stafford, England
 
you don't need remotedesktop, you need Access Data Projects.

from what I understand, you merely want to use the persons real time--
from their local machine-- but using the remote desktop is not
allowing you to use this functionality.

Perhaps if you followed best practices, and adopted Access Data
Projects (to run over the WAN) you would have a much much much simpler
arcthitecture.

ADP works fantastic over the WAN.
It's just the JET part of MS Access that sucks,

-Aaron
 
Hi Aaron,

I am a big advocate of ADP, so if that was an option, I would use it, but
unfortunately, it's not an option.

Rick

message
you don't need remotedesktop, you need Access Data Projects.

from what I understand, you merely want to use the persons real time--
from their local machine-- but using the remote desktop is not
allowing you to use this functionality.

Perhaps if you followed best practices, and adopted Access Data
Projects (to run over the WAN) you would have a much much much simpler
arcthitecture.

ADP works fantastic over the WAN.
It's just the JET part of MS Access that sucks,

-Aaron
 
Thanks Doug,

At first glance, it looks like that will do the job, but will have to have
some time to look at it more closely. If that doesn't work, then I'll try
your solution Ken.

Thanks!~

Rick
 
Back
Top