Converting Local Time to Universal Coordinated Time

[Jim Ory]

WinXP and Access2002

For world time, only one source is acceptable, Universal Coordinated Time.
It is the same as Greenwich Mean Time. Our computers are based on this time
and we can pick which time zone we are in so the correct time will be on our
screens.

My main form consists of "customers", based on tblCustomers and a sub-form,
based on tblOperations, linked via 'customerID'.

On the sub-form for 'operations' I have three (date/time) text boxes bound
to 'operations' table. (Formatted to display the proper date or time.) One
for the date of the operation, one for the start of operation, and one for
the end of operation.

When I click on each text box, using a macro, I can set each to record; day,
time start, time end. Twice a year I have to modify the macros to add either
4 or 5 hours to Now(). This is the code: DateAdd("h",+4,Now()), or
DateAdd("h",+5,Now()). This works alright and I can easily change the actual
time via the keyboard if it does not correlate to the time I need to input.

My question is, however, how can I make an option group to select between
standard time and daylight savings time (this is the easy part), then use the
option group in the main form to make the selection, standard time or savings
time, so that when I click on the date fields of the sub-form the correct
number of hours will be added to Now()? (And thereby show the time in UTC
format.)

My first thought was make a table to store the results of the option group.
Then write VBA to access the table, return the stored option number (1 or 2),
use that number to apply the proper number of hours in the code to the 'On
Click' event of the time text boxes in the sub-form. Where do I place the
'option group'? Do I need to query the table for the option group result?
Then how do I implement the returned value? This is where I'm struggling.

My next question is; am I thinking that this is the best way to accomplish
this? Or do I need to reassess the means?

Any suggestions are, of course, welcome.

 

[Dirk Goldgar]

WinXP and Access2002

For world time, only one source is acceptable, Universal Coordinated
Time. It is the same as Greenwich Mean Time. Our computers are based
on this time and we can pick which time zone we are in so the correct
time will be on our screens.

My main form consists of "customers", based on tblCustomers and a
sub-form, based on tblOperations, linked via 'customerID'.

On the sub-form for 'operations' I have three (date/time) text boxes
bound to 'operations' table. (Formatted to display the proper date or
time.) One for the date of the operation, one for the start of
operation, and one for the end of operation.

When I click on each text box, using a macro, I can set each to
record; day, time start, time end. Twice a year I have to modify the
macros to add either 4 or 5 hours to Now(). This is the code:
DateAdd("h",+4,Now()), or DateAdd("h",+5,Now()). This works alright
and I can easily change the actual time via the keyboard if it does
not correlate to the time I need to input.

My question is, however, how can I make an option group to select
between standard time and daylight savings time (this is the easy
part), then use the option group in the main form to make the
selection, standard time or savings time, so that when I click on the
date fields of the sub-form the correct number of hours will be added
to Now()? (And thereby show the time in UTC format.)

My first thought was make a table to store the results of the option
group. Then write VBA to access the table, return the stored option
number (1 or 2), use that number to apply the proper number of hours
in the code to the 'On Click' event of the time text boxes in the
sub-form. Where do I place the 'option group'? Do I need to query the
table for the option group result? Then how do I implement the
returned value? This is where I'm struggling.

My next question is; am I thinking that this is the best way to
accomplish this? Or do I need to reassess the means?

Any suggestions are, of course, welcome.

It's a different approach, but maybe this routine will be useful:

'----- start of code -----
Option Compare Database
Option Explicit

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

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

Function NowUTC() As Date

Dim st As SYSTEMTIME

GetSystemTime st

With st
NowUTC = _
DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

'----- end of code -----

I am *not* an expert on internationalization, but I went to my trusty
API Guide, downloaded some time ago from AllAPI.net, where it says, "The
GetSystemTime function retrieves the current system date and time. The
system time is expressed in Coordinated Universal Time (UTC)." So I
*think* you could use the above-defined NowUTC function to return and
store the UTC date/time directly, rather than worrying about converting
local time to UTC.

 

[Jim Ory]

Dirk,

That piece of code is perfect. I also used it to display the UTC time in
[lblClock] in the form header, as well as in my date/time fields.

Thank you very much.

 

[Scuda]

Hi All, after a search, I found this result to my answer but have a question.

I want to have a text box that gives me the UTC, where do I apply this code?
It threw me off because it had the Option Compare Database in it which to me
means the whole thing.

Thanks in advance,
Steph.

"> > > For world time, only one source is acceptable, Universal Coordinated

 

[Jim Ory]

Hi Steph.

I have a text box in the form header named "lblclock" where the UTC time is
displayed.

The code is in a module named "mdeUTC" and the function name is "NowUTC".

In the form's properties, event 'On timer', VBA code: Me!lblClock.Caption =
"UTC: " & Format(NowUTC, "dddd, mmm d yyyy, hh:mm:ss"). This gives you UTC.

If you want local time, then the code is: Me!lblClock.Caption = "UTC: " &
Format(Now, "dddd, mmm d yyyy, hh:mm:ss")

In the form's properties, "Timer Interval" = 1000.

I'm not a pro, but I think you could use the "Select Case" code to switch
between the two formats, or perhaps use an "Option Group". I don't know what
would be best.

 

[Jim Ory]

Steph,

I added an option group to my form, called 'optTime', then added this code
to the form's timer event. It works for me.

If Me.optTime = 1 Then
Me!lblClock.Caption = "UTC: " & Format(NowUTC, "dddd, mmm d yyyy,
hh:mm:ss")
ElseIf Me.optTime = 2 Then
Me!lblClock.Caption = "Local: " & Format(Now, "dddd, mmm d yyyy,
hh:mm:ss")
End If

Clicking on either option changes the clock immediately.

 

[Scuda]

Jim, thanks so much for the info!

I am getting a Syntax error however:

Private Sub Form_Timer()
Me!lblClock.Caption = "UTC: " &
Format(Now, "dddd, mmm d yyyy, hh:mm:ss")

End Sub

I changed the form's properties as you stated.

Thanks again!
Here is my module VBA code:

Option Compare Database
Option Explicit

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

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

Function NowUTC() As Date

Dim st As SYSTEMTIME

GetSystemTime st

With st
NowUTC = _
DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

 

[Jim Ory]

Private Sub Form_Timer()
Me!lblClock.Caption = "UTC: " &
Format(Now, "dddd, mmm d yyyy, hh:mm:ss")

End Sub

I'm guessing that your "me.lblClock.Caption . . . .etc." line is not on one
line. I think I forgot to add the underscore when I pasted it into this
window. The line I am referring to needs to be on the same line. If you
copied Dirk's VBA code correctly, then I'd look at the timer event code above
for the correction of the syntax error.

 

[Scuda]

Hi Jim, I am sorry to be a pain, but I keep having a problem. I tried the
optTime code as well and in both the code immediately turns red and I am
getting an Expected End in Statement error.

thanks.

 

[Jim Ory]

Steph,

Here is the code I am using:

Line: On Error GoTo ProcError
Line: If Me.optTime = 1 Then
Line: Me!lblClock.Caption = "UTC: " & Format(NowUTC, "dddd, mmm d yyyy,
hh:mm:ss")
Line: ElseIf Me.optTime = 2 Then
Line: Me!lblClock.Caption = "Local: " & Format(Now, "dddd, mmm d yyyy,
hh:mm:ss")
Line: End If
Line: ExitProc:
Line: Exit Sub
Line: ProcError:
Line: MsgBox "error" & Err.Number & ": " & Err.Description
Line: Resume ExitProc

There should be only 11 lines of code, not including the "Private Sub" and
"End Sub". I've included error processing.

You can copy and paste the code. Remove all "Line:"s. I put those in to help
make it clearer where the lines of code start and end.

This text box that we use here tends to wrap the text. When you paste the
code, remove the wrapping effect in the code window.

 

[Scuda]

Jim, again thank you for your patience!

I did everything you said, even started a blank form in case there was a
conflict somewhere. After doing all you suggested, I am getting this error:

Error438 - Object doesn't support this Property or Object

This is what is in my mdeUTC:

Option Compare Database
Option Explicit

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

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

Function NowUTC() As Date

Dim st As SYSTEMTIME

GetSystemTime st

With st
NowUTC = _
DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

This is on my forms OnTimer event (timer is set to 1000 also):

Private Sub Form_Timer()
On Error GoTo ProcError
If Me.optTime = 1 Then
Me!lblclock.Caption = "UTC: " & Format(NowUTC, "dddd, mmm d yyyy, hh: mm: ss
")
ElseIf Me.optTime = 2 Then
Me!lblclock.Caption = "Local: " & Format(Now, "dddd, mmm d yyyy, hh: mm: ss ")
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "error" & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Here is my optTime option group:

Name: optTime
no control source

My 2 buttons, labeled UTC and LOCAL respectfully. (name and caption)

And my lblclock text box:

Name: lblclock
no control source

Not sure what I am doing wrong! Thanks again for your patience and kindness.

Steph

 

[Jim Ory]

Steph,

If I understand correctly, you can't get out of the Visual Basic Window
without the error.

I'm using Access2K2, so the following References may have different #s
depending on your Version of Access.

Open the Visual Basic Window | Tools | References. The following references
need their respective check boxes selected. It may be that the last reference
has not been selected. (I'm guessing.) In Access2K2 it is not automatically
checked, so if that's the case in your version, then go through the list,
locate the 'DAO Object Library' and give it a check mark. Close the
"References" window and then reopen to make sure you have all these checked.
Close all, Save, Debug.

Visual Basic For Applications
Microsoft Access 10.0 Object Library (Access2K is 9.0, 3K is 11.0)
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library (This may be a different #.)
Microsoft DAO 3.6 Object Library (This may be a different #)

 

[Scuda]

Hi Jim, those are all checked. I am using 2003. The last error I recieved,
the 438, was when I switched to form view from within Access.

Thanks again.

 

[Jim Ory]

Hi Steph,

If you run 'Debug' from the Visual Basic Window, it will find the line with
the incorrect syntax, which is what your problem sounds like to me. If you
are just closing the Visual Basic Window and opening the Form and you are
getting the error message, you will find it very difficult to find the error.

Go into the Visual Basic Editor Window and run 'Debug'. Leaving quotes out
or parens out will create havoc. Sometimes spaces. If 'Debug' does not find
the syntax error, i.e. the line of code turns red, then you'll have to look
elsewhere.

 

[Scuda]

Hi Jim, I had run that before but ran it again and found a few errors I
didn't know about but now I am clean and the code you gave me is in there and
NO RED, and looks good, didn't return any errors.

Getting ready to give up! Thanks again.