[RESOLVED] VB and MS Access

[zapper222]

This may be more of an Access question than a VB question but I am not overly familiar with either so I am trying to cover all bases.

I need to convert local time to UTC (GMT) in a MS Access application.

The application presently uses a simple VB routine to add either 3 or 4 hours to the local time depending on whether we are on DST or EST time. The problem is that we have to manually change this 3 to a 4 and vice versa in the spring and the fall.

VB has a built in to handle this as shown below.

Dim saveUtcNow As DateTime = DateTime.UtcNow

This would give me the UTC time in saveUtcNow.

When I try to get this VB code into MS Access it won't buy it. I am wondering if I need to include some other code ( a further declaration or an import ) into the MS Access VB window to get it to work.

Thanks
Zapper

[dglienna]

You could convert it to a string. What version of Access are you using?

[TechGnome]

Wait... are you saying you're trying to use this code inside of Access?
Dim saveUtcNow As DateTime = DateTime.UtcNow


That looks like VB.NET.... while Access uses VBA which is a subset of the older VB6 set. So the above line isn't going to work in Access.

-tg

[zapper222]

MS Access 97

The difficulty is VB in Access is not behaving the same as VB in MS studio.
The following works in MS Studio VB

Dim date1 As Date
date1 = Date.UtcNow

but it won't work in VB for Access. I get an error on the "Date.UtcNow"

I am not sure if Access has its own version of VB or whether it can be directed to use the version that MS studio is using ??

[dglienna]

Here's an Excel Macro that should work:

Option Explicit

Public Declare Function SystemTimeToFileTime Lib _
  "kernel32" (lpSystemTime As SYSTEMTIME, _
  lpFileTime As FILETIME) As Long

Public Declare Function LocalFileTimeToFileTime Lib _
  "kernel32" (lpLocalFileTime As FILETIME, _
  lpFileTime As FILETIME) As Long

Public Declare Function FileTimeToSystemTime Lib _
  "kernel32" (lpFileTime As FILETIME, lpSystemTime _
  As SYSTEMTIME) As Long

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

Public 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
This may look imposing, as is often the case when working with system calls, but it works wonderfully. There are three system routines referenced (SystemTimeToFileTime, LocalFileTimeToFileTime, and FileTimeToSystemTime). By setting up the calls and using them in order, the date and time are automatically adjusted to GMT. To use the function, in your worksheet you would enter this to convert the time in cell B7:

=localtimetoutc(B7)

[zapper222]

Thanks man, that should do what I need.

Zapper