Convert UTC GMT to Local Time Coding Error

[BIGf00t]

Hello! I borrowed this coding from accessmonster.com and it looks good, however when I try to compile it I get a "Compile Error: User-defined type not defined," and it stops at the first line of code on the Private Declare. I am not familiar with this error, but I imagine it is calling a kernel function that I don't have, so I went to tools, references, and added the Microsoft Office 14.0 Object Library, but that didn't do it. I am in Access 2010. Your help is much appreciated!

Here's the code:


Option Explicit

Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" _
(lpTimeZoneInformation As TimeZoneInfo, lpUniversalTime As SystemTime, lpLocalTime As SystemTime) As Long

Public Function fConvertUTCtoLocalTime(pUTC As Date) As Date
On Error GoTo Err_fConvertUTCtoLocalTime
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
Dim stUTC As SystemTime
Dim stLocal As SystemTime

'Get TimeZone information
lngRet = GetTimeZoneInformation(udtTZI)

stUTC.intYear = Year(pUTC)
stUTC.intMonth = Month(pUTC)
stUTC.intDay = Day(pUTC)
stUTC.intHour = Hour(pUTC)
stUTC.intMinute = Minute(pUTC)
stUTC.intSecond = Second(pUTC)
stUTC.intMilliseconds = 0

'Converts a time in Coordinated Universal Time (UTC) to a specified time zone 's corresponding local time.
'It takes into account whether the time to be converted falls in daylight saving time or not

lngRet = SystemTimeToTzSpecificLocalTime(udtTZI, stUTC, stLocal)

'Return Date type
fConvertUTCtoLocalTime = DateSerial(stLocal.intYear, stLocal.intMonth, stLocal.intDay) + TimeSerial(stLocal.intHour, stLocal.intMinute, stLocal.intSecond)

Exit_fConvertUTCtoLocalTime:
Exit Function

Err_fConvertUTCtoLocalTime:
MsgBox Err.Description
Resume Exit_fConvertUTCtoLocalTime
End Function

 

[HiTechCoach]

I created this example that may help: Time Zones

 

[BIGf00t]

Thank you for the reply! That is an informative link, but my problem is not with calculating DST, I believe this coding solves that. The problem I need help with is the recurring "Compile Error." Thanks in advance for any suggestions anyone may have!

 

[HiTechCoach]

Thank you for the reply! That is an informative link, but my problem is not with calculating DST, I believe this coding solves that. The problem I need help with is the recurring "Compile Error." Thanks in advance for any suggestions anyone may have!

The example I gave you definitely handles DST. See the screen shot attached.

What you posted is incomplete code. It is missing the the code to declare the data types for TimeZoneInfo and SystemTime.

The example I gave you has has the missing code you need. That is the reason I pointed you to that example. Look at eh code behind the form. At the top of the VBA code you will find the missing declarations for imeZoneInfo and SystemTime that you should be able to adapt to your VBA code.

 

[HiTechCoach]

I pasted your code into my Example. After a few names changes to your code to match the names in my code it worked.

Here is what appears to be missing from the code you posted.

Private Type SystemTime
   intYear         As Integer
   intMonth        As Integer
   intDayOfWeek    As Integer
   intDay          As Integer
   intHour         As Integer
   intMinute       As Integer
   intSecond       As Integer
   intMilliseconds As Integer
End Type

Private Type TimeZoneInfo
   Bias As Long
   StandardName(0 To 63) As Byte  'unicode (0-based)
   StandardDate As SYSTEMTIME
   StandardBias As Long
   DaylightName(0 To 63) As Byte  'unicode (0-based)
   DaylightDate As SYSTEMTIME
   DaylightBias As Long
End Type

Note: I changed the names to match the code you originally posted.

 

[BIGf00t]

Thank you so much for your help!

 

[HiTechCoach]

You're welcome!

Glad I could assist.