Convert time to UTC

[cstimart]

If found some code (which utilizes kernel32) I can use in Excel to convert local time to UTC. However, when I try to implement in Access 2007, I get a compile error, as it doesn't recognize kernel32. I've tried to find how to resolve with references, etc...but no luck.

Any ideas w/ or w/o kernel32?

 

[mrmmickle1]

I think Chip Pearson has you covered. Have a look here: CLICK HERE Don't see any reference to Kernel32. GMT and UTC are the same thing....

 

[cstimart]

Thanks, that's similar to the code I found, but it does call the API.

Private Declare Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
Private Declare Sub GetSystemTime Lib "kernel32" _
        (lpSystemTime As SYSTEMTIME)

It works in Excel, but not Access.

 

[xenou]

Where did you place the code in Access.
In general, should be in a standard module and as a rule Access does require the PUBLIC keyword on the function you are trying to call from your forms/queries.

 

[cstimart]

My Bad!!!

I forgot to add the following types

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type
Public 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

 

[mrmmickle1]

cstimart,

Glad you were able to get it working. Would you mind posting the full working code for the benefit of the other forum members. Just in case someone else comes across the same issue.

Xenou,

Hope your doing well. Thanks for the additional detail. I'm sure someone will find that tidbit useful!

 

[cstimart]

Here's my code that I've used/found.

Declare Function SystemTimeToFileTime Lib "kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
Declare Function LocalFileTimeToFileTime Lib "kernel32" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long
Declare Function FileTimeToSystemTime Lib "kernel32" (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long

Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

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 LocalTimeToUTC(dteTime As Date) As Date
    Dim dteLocalFileTime As FILETIME
    Dim dteFileTime As FILETIME
    Dim dteLocalSystemTime As SYSTEMTIME
    Dim dteSystemTime As SYSTEMTIME
    dteLocalSystemTime.wYear = CInt(Year(dteTime))
    dteLocalSystemTime.wMonth = CInt(Month(dteTime))
    dteLocalSystemTime.wDay = CInt(Day(dteTime))
    dteLocalSystemTime.wHour = CInt(Hour(dteTime))
    dteLocalSystemTime.wMinute = CInt(Minute(dteTime))
    dteLocalSystemTime.wSecond = CInt(Second(dteTime))
    Call SystemTimeToFileTime(dteLocalSystemTime, dteLocalFileTime)
    Call LocalFileTimeToFileTime(dteLocalFileTime, dteFileTime)
    Call FileTimeToSystemTime(dteFileTime, dteSystemTime)
    LocalTimeToUTC = CDate(dteSystemTime.wMonth & "/" & _
      dteSystemTime.wDay & "/" & _
      dteSystemTime.wYear & " " & _
      dteSystemTime.wHour & ":" & _
      dteSystemTime.wMinute & ":" & _
      dteSystemTime.wSecond)
End Function