Greenwich Mean Time (GMT) in Microsoft Access 2003

[tpers]

How can I convert a datetime value (i.e. now()) to Greenwich Mean Time(GMT)
in Microsoft Access 2003?

I have an Access database that is shared by a few people across different
time zones. The database has a form that is time sensitive. The time field
is in the Pacific time zone. By using the GMT value for the NOW() and the
Time-Field, I am hoping that my friend in the Central zone can get the
appropriate record based on his time zone, when the form opens.

Is there another (easier) way to accomplish this task?

I appreciate any help available.
Thank you,
Tom.

[lylefair]

This is quick air code cobbled up from another function but it may give
you a start.

Const TIME_ZONE_ID_INVALID As Long = &HFFFFFFFF
Const TIME_ZONE_ID_UNKNOWN As Long = &H0
Const TIME_ZONE_ID_STANDARD As Long = &H1
Const TIME_ZONE_ID_DAYLIGHT As Long = &H2

Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(32) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(32) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Private Declare Function GetTimeZoneInformation _
Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) _
As Long

Public Function Zulu() As Date
Dim tzi As TIME_ZONE_INFORMATION
If (GetTimeZoneInformation(tzi) And TIME_ZONE_ID_STANDARD) _
Or _
(GetTimeZoneInformation(tzi) And TIME_ZONE_ID_DAYLIGHT) Then
Zulu = DateAdd("n", tzi.Bias, Now())
Else
MsgBox "Time Zone Invalid or Unknown"
Zulu = Now()
End If
End Function

[Arno R]

"tpers" <tp***@nyc.rr.com> schreef in bericht news:2a****************@twister.nyc.rr.com...

How can I convert a datetime value (i.e. now()) to Greenwich Mean Time(GMT)
in Microsoft Access 2003?

I have an Access database that is shared by a few people across different
time zones. The database has a form that is time sensitive. The time field
is in the Pacific time zone. By using the GMT value for the NOW() and the
Time-Field, I am hoping that my friend in the Central zone can get the
appropriate record based on his time zone, when the form opens.

Is there another (easier) way to accomplish this task?

I appreciate any help available.
Thank you,
Tom.

Tom,
I think I would use a kind of TimeShift-table for this.
-- You know the locations where your db is used.
-- You know the TimeShift in positive or negative hours of that location.

On startup you could 'ask' for the location (and/or store this in a global var or txtfile).

Function ZoneDateTime(TimeShift)
ZoneDateTime = Now() + gTimeShift / 24
End Function

Function ZoneTime(TimeShift)
ZoneTime = Time() + gTimeShift / 24
End Function

Arno R

[dbahooker]

i would definitely convert it to GMT before writing it instead of
waiting for it to be changed.. it just seems like something that is so
complex that you'd hate to mismatch times.. and the timezone
information is actually quite frigging complex.. there are some
counties that dont use daylight savings time for example.. right?

i would start with just rewriting the DATE() and NOW() functions and
find some API call to get 'what time zone am i in'

[Chuck Grimsby]

On Mon, 26 Sep 2005 02:37:50 GMT, "tpers" <tp***@nyc.rr.com> wrote:

How can I convert a datetime value (i.e. now()) to Greenwich Mean Time(GMT)
in Microsoft Access 2003?
I have an Access database that is shared by a few people across different
time zones. The database has a form that is time sensitive. The time field
is in the Pacific time zone. By using the GMT value for the NOW() and the
Time-Field, I am hoping that my friend in the Central zone can get the
appropriate record based on his time zone, when the form opens.
Is there another (easier) way to accomplish this task?
I appreciate any help available.

Lyle's advice is the best so far in this thread (everyone else is
making it far more difficult then it needs to be, IMHO), *however*,
that said, be aware that no matter what you do you are going to get
"inconsistent" results. Everyone's clock is set to different times,
and depending upon when they last updated their computer's internal
clock and the rate at which their computer loses or gains time, it
won't be accurate to a central point.

The _best_ way to do this is to use a SQL Server Database and have the
Server set the time stamp. You'll have to regularly update the SQL
Server's clock, of course, but it will be _far_ more consistent then
having a bunch of computers writing what they hope the time is to the
database!

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[lylefair]

Chuck Grimsby wrote:

Everyone's clock is set to different times,
and depending upon when they last updated their computer's internal
clock and the rate at which their computer loses or gains time, it
won't be accurate to a central point.

The _best_ way to do this is to use a SQL Server Database and have the
Server set the time stamp. You'll have to regularly update the SQL
Server's clock, of course, but it will be _far_ more consistent then
having a bunch of computers writing what they hope the time is to the
database!

This is a good idea, provided the users are all connected to something
from which they can determine their agreed upon system time.

For those who want to use the registry, depsite the valid warnings you
give, I have written a simpler (I think) function to get GMT.

Option Explicit

Private Declare Function RegOpenKey Lib "advapi32.dll" _
Alias "RegOpenKeyA" _
(ByVal Key As Long, _
ByVal SubKey As String, _
Result As Long) _
As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
Alias "RegQueryValueExA" (ByVal Key As Long, _
ByVal ValueName As String, _
ByVal Reserved As Long, _
ByVal TypeIsAReservedWord As Long, _
Data As Long, _
DataLength As Long) _
As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal Key As Long) _
As Long

Private Const HKEY_LOCAL_MACHINE = &H80000002
Public Function ActiveTimeBiasinMinutes() As Long
Dim Key As Long
Dim KeyName As String
Dim Length As Long
Dim ReturnValue As Long

On Error GoTo ActiveTimeBiasinMinutesErr

KeyName = "SYSTEM\CurrentControlSet\Control\TimeZoneInformat ion"
Length = 4

ReturnValue = RegOpenKey(HKEY_LOCAL_MACHINE, KeyName, Key)

If ReturnValue = 0 Then
ReturnValue = RegQueryValueEx(Key, _
"ActiveTimeBias", _
0, _
0, _
ActiveTimeBiasinMinutes, _
Length)
End If

If ReturnValue <> 0 Then Resume ActiveTimeBiasinMinutesErr

ActiveTimeBiasinMinutesExit:
On Error Resume Next
RegCloseKey Key
Exit Function
ActiveTimeBiasinMinutesErr:
MsgBox "ActiveTimeBiasinMinutes not retrieved!", vbCritical,
"Error!"
ActiveTimeBiasinMinutes = 0
Resume ActiveTimeBiasinMinutesExit
End Function

Public Function Zulu()
Zulu = DateAdd("n", ActiveTimeBiasinMinutes(), Now())
End Function

Sub test()
MsgBox ActiveTimeBiasinMinutes()
MsgBox Zulu()
End Sub

[MGFoster]

lylefair wrote:

Chuck Grimsby wrote:

Everyone's clock is set to different times,
and depending upon when they last updated their computer's internal
clock and the rate at which their computer loses or gains time, it
won't be accurate to a central point.

The _best_ way to do this is to use a SQL Server Database and have the
Server set the time stamp. You'll have to regularly update the SQL
Server's clock, of course, but it will be _far_ more consistent then
having a bunch of computers writing what they hope the time is to the
database!

This is a good idea, provided the users are all connected to something
from which they can determine their agreed upon system time.

For those who want to use the registry, depsite the valid warnings you
give, I have written a simpler (I think) function to get GMT.

Option Explicit

Private Declare Function RegOpenKey Lib "advapi32.dll" _
Alias "RegOpenKeyA" _
(ByVal Key As Long, _
ByVal SubKey As String, _
Result As Long) _
As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
Alias "RegQueryValueExA" (ByVal Key As Long, _
ByVal ValueName As String, _
ByVal Reserved As Long, _
ByVal TypeIsAReservedWord As Long, _
Data As Long, _
DataLength As Long) _
As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal Key As Long) _
As Long

Private Const HKEY_LOCAL_MACHINE = &H80000002
Public Function ActiveTimeBiasinMinutes() As Long
Dim Key As Long
Dim KeyName As String
Dim Length As Long
Dim ReturnValue As Long

On Error GoTo ActiveTimeBiasinMinutesErr

KeyName = "SYSTEM\CurrentControlSet\Control\TimeZoneInformat ion"
Length = 4

ReturnValue = RegOpenKey(HKEY_LOCAL_MACHINE, KeyName, Key)

If ReturnValue = 0 Then
ReturnValue = RegQueryValueEx(Key, _
"ActiveTimeBias", _
0, _
0, _
ActiveTimeBiasinMinutes, _
Length)
End If

If ReturnValue <> 0 Then Resume ActiveTimeBiasinMinutesErr

ActiveTimeBiasinMinutesExit:
On Error Resume Next
RegCloseKey Key
Exit Function
ActiveTimeBiasinMinutesErr:
MsgBox "ActiveTimeBiasinMinutes not retrieved!", vbCritical,
"Error!"
ActiveTimeBiasinMinutes = 0
Resume ActiveTimeBiasinMinutesExit
End Function

Public Function Zulu()
Zulu = DateAdd("n", ActiveTimeBiasinMinutes(), Now())
End Function

Sub test()
MsgBox ActiveTimeBiasinMinutes()
MsgBox Zulu()
End Sub

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's what I use to get GMT:

Private Const LOCALE_SYSTEM_DEFAULT& = &H800

'**********************************
'** Type Definitions:

Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare Sub GetSystemTime Lib "Kernel32" (lpSystemTime As
SYSTEMTIME)

Private Declare Function GetTimeFormat& Lib "Kernel32" Alias _
"GetTimeFormatA" (ByVal Locale As Long, ByVal dwFlags As Long, _
lpTime As SYSTEMTIME, ByVal lpFormat As Long, _
ByVal lpTimeStr As String, ByVal cchTime As Long)

Public Function GetZuluTime() As Date

Dim myTime As SYSTEMTIME
Dim strBuffer As String
Dim lng As Long
Dim strSystemTime As String

GetSystemTime myTime
strBuffer = String$(255, Chr$(0))
lng = GetTimeFormat&(LOCALE_SYSTEM_DEFAULT, 0, myTime, _
0, strBuffer, 254)
With myTime
GetZuluTime = DateSerial(.wYear, .wMonth, .wDay) _
+ CDate(strBuffer)
End With

End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzl00YechKqOuFEgEQICLACfbQp5Z0AlvoVKPCBkH/o7DNZPGPEAoJ3l
cyvX0iE9s2JgYdwB16OtfLm+
=z3y8
-----END PGP SIGNATURE-----

[Chuck Grimsby]

On 27 Sep 2005 08:23:01 -0700, "lylefair" <ly******@yahoo.ca> wrote:

Chuck Grimsby wrote:

Everyone's clock is set to different times,
and depending upon when they last updated their computer's internal
clock and the rate at which their computer loses or gains time, it
won't be accurate to a central point.
The _best_ way to do this is to use a SQL Server Database and have the
Server set the time stamp. You'll have to regularly update the SQL
Server's clock, of course, but it will be _far_ more consistent then
having a bunch of computers writing what they hope the time is to the
database!

This is a good idea, provided the users are all connected to something
from which they can determine their agreed upon system time.
For those who want to use the registry, depsite the valid warnings you
give, I have written a simpler (I think) function to get GMT.
Option Explicit
Private Declare Function RegOpenKey Lib "advapi32.dll" _
Alias "RegOpenKeyA" _
(ByVal Key As Long, _
ByVal SubKey As String, _
Result As Long) _
As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
Alias "RegQueryValueExA" (ByVal Key As Long, _
ByVal ValueName As String, _
ByVal Reserved As Long, _
ByVal TypeIsAReservedWord As Long, _
Data As Long, _
DataLength As Long) _
As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal Key As Long) _
As Long
Private Const HKEY_LOCAL_MACHINE = &H80000002
Public Function ActiveTimeBiasinMinutes() As Long
Dim Key As Long
Dim KeyName As String
Dim Length As Long
Dim ReturnValue As Long
On Error GoTo ActiveTimeBiasinMinutesErr
KeyName = "SYSTEM\CurrentControlSet\Control\TimeZoneInformat ion"
Length = 4
ReturnValue = RegOpenKey(HKEY_LOCAL_MACHINE, KeyName, Key)

If ReturnValue = 0 Then
ReturnValue = RegQueryValueEx(Key, _
"ActiveTimeBias", _
0, _
0, _
ActiveTimeBiasinMinutes, _
Length)
End If
If ReturnValue <> 0 Then Resume ActiveTimeBiasinMinutesErr
ActiveTimeBiasinMinutesExit:
On Error Resume Next
RegCloseKey Key
Exit Function
ActiveTimeBiasinMinutesErr:
MsgBox "ActiveTimeBiasinMinutes not retrieved!", vbCritical,
"Error!"
ActiveTimeBiasinMinutes = 0
Resume ActiveTimeBiasinMinutesExit
End Function
Public Function Zulu()
Zulu = DateAdd("n", ActiveTimeBiasinMinutes(), Now())
End Function
Sub test()
MsgBox ActiveTimeBiasinMinutes()
MsgBox Zulu()
End Sub

Pretty much the same code I use when doing this..... I was just too
lazy to post it.
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing