Formatting Date/Time for a specific language and country in Access and VBA

by Philipp Stiefel, originally published August 1st, 2019, last updated August 1st, 2019

Based on images by amber_avalona and clker-free-vector-images, used here under CC0 licensing

In my elaboration on the Date/Time data type in Access and VBA, I quite strongly recommend not to use any fixed date formats in an international environment, not in any environment actually, except for reports and data exports and imports.

In export and import scenarios you most likely need to handle plain numeric date formats only. So, the above-mentioned exception can be implemented by using a hardcoded date or time format for the Format-Function in Access. – Simple and straight-forward.

The difficulties of printing dates in a foreign language

Reporting however, can be a quite different beast. Often there is a requirement to print (or display) a long date format including the full name of the day or the month (e.g. “Wednesday, July 31, 2019”). This is also easy to implement in VBA and Microsoft Access with Format, but only if the desired output language and country matches the current regional settings of the user running Access. If the output language is different, this becomes somewhat more difficult…

A simple approach to solve this is would be to create a small VBA function that takes the day of week as an argument and returns the full name of the day in the desired output language. The same can be done for the months. There are only 7 days and 12 months after all. – This would be appropriate for one single know target language and format.

But now imagine, you need the output in different languages and different, country-specific formats, depending on the report’s recipient’s language and country. - Things would get quite complicated then.

It doesn’t have to be this way. There is an easy way out. – It’s, once again, the Windows API. Windows supports about 200 different locales that define the output language as well as the format of dates and numbers.

Using the GetDateFormatEx and GetTimeFormatEx functions in VBA

The Windows API provides several functions to get information about the NLS (National Language Support) settings. But most important for us and our requirements in this context are the two functions GetDateFormatEx and GetTimeFormatEx in the Kernel32 Windows library.

These two functions work similar to the Format-Function in Access and VBA. There is, however, one fundamental difference. They allow the developer to specify the locale they want the output to be in. – For the rest of this article, I will only focus on GetDateFormatEx because both functions work analogously and GetDateFormatEx is certainly much more interesting than GetTimeFormatEx.

The raw declaration of GetDateFormatEx looks like this:

The very important point is to notice the W character in string data type declarations here. It indicates the string argument is supposed to be a Unicode (UTF-16) String. This is not a big deal because VBA Strings are also Unicode. However, you need to be aware that the default marshalling (data type setup and data exchange) of strings from VBA to the Windows API is done by using ANSI strings.

Here is my corresponding declaration in VBA:

Note that I declared the Unicode strings mentioned above not as strings but as LongPtr types.

Based on that declaration, I wrote a small wrapper function to call the Windows API function. There I do not pass the VBA strings directly to the API (that would convert them to ANSI strings) but instead use the StrPtr-Function to explicitly pass a pointer to my VBA string without string marshalling.

The original API as well as my wrapper functions support either one of a couple of predefined date formats like ShortDate or LongDate (I grouped them in a user defined Enum DateFormat) or a custom format string, called Format Picture for date output. These are roughly similar to the Format argument of the Format Function in VBA/Access. The most important difference though, is that the Windos API Format Pictures are case sensitive and use an upper-case M for month and a lower case m for minute in the time Format Pictures.

The target locale can be either neutral language identifier as just “de” or it can include the country as well as in “de-AT”.

Here are some samples how the function can be called from the Immediate Pane:

Here is a screenshot of the results of a query based on a table with the locale identifiers passed into the FormatDateForLocale-function shown above. The column LongDateInLocale shows the return value with the LongDate standard format while the MonthInLocale column uses a MMMM custom date format picture.

Conclusion

The screenshot above shows quite impressively that not only the day and month names are translated automatically but also the date is automatically formatted according to local customs. Even the year is adjusted if the country uses a calendar different from our Gregorian Calendar, as shown above for Arabian and Thai.

The Windows API date formatting functions discussed here, are invaluable if you need to format dates in Access for any language or country different from your own Windows regional settings. If you need to target various languages and countries the amount of work that would be required to implement that on your own in VBA is almost insurmountable. With the wrapper functions shown here it becomes trivial to implement.

Download

You can download my date format sample database including the query and function discussed in this article and use it in your own projects. If you want to use these functions in another VBA enabled application, you can also download the VBA module containing the date format functions standalone.

Share this article: