In this article I will be listing out a set of VB functions to convert GMT time in MS Access. If you are creating linked tables in MS Access to larger enterprise applications or data warehouses, more often than not the time is stored in GMT time. This is a standard practice, and if you are ever building an Access database used across multiple time zones then this may be something to look in to.

By setting everything to a standard time and converting to/from, it will make it much easier to manage the data when reporting on it. Now I know you can just add/subtract hours against a time stamp, but what if you are building spans multiple time zones and you need to convert to each based on who is looking at the data? For instance, you need to generate a report on daily transactions/results for facilities China, Los Angeles, New York, and London?

Ok, I won’t go in to a bunch of scenarios as chances are if you have gotten this far, or stumbled upon this page, you are looking how to convert GMT and know the reasons why you need to do it. So here we go…

Of course, if you want to just download the complete solution you know I’ll provide that as well. So here you go.

[download id=”9″ format=”1″]

Convert GMT Time in MS Access

So before we get going on converting time between GMT and local time, we have to establish some UDT’s and call some DLL functions from VB. These items are key in allowing us to perform the time conversions in VB. So in short, make sure you copy these to your module!

Creating the User-Defined Types (UDT) Data Structures

If you aren’t familiar with a UDT, this is how VB allows us to create our own data structures. Here, we will define System Time, Time Zone Information, and Time Zone data types. Since these don’t exist, we have to create them all by ourselves.

Calling the DLL Functions to get System Time & Time Zone

The declare statement tells VBA we want to include the definition for a DLL function in the project. In this case we want to obtain the time zone and system time information functions in that DLL.

Creating the Functions to Convert the Time

Ok, now that we have the above items out of the way we can get the functions in place and start converting time. These sets of functions will do a number of things like converting to/from GMT and local time, performing time calculations between time zones, etc.

Convert Local Time to GMT

This formula will take the current local system time and convert it to GMT time. You can also adjust for daylight savings time within this function if the time zone you are converting from adjusts for DST.

Convert GMT to Local Time

Much like the above function, this does the exact opposite in which it converts GMT time to local time.

Convert System Time to VB Date Value

This function will allow you to convert the SystemTime structure to a VB/VBA date value.

Determine Hours & Minutes between Local Time and GMT

This function will determine the number of hours and minutes (depending on AsHours Boolean) between local time and GMT.

Return the Result of TIME_ZONE enum

This function returns a result that is a member of the TIME_ZONE enum.

Tags: