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.
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.
Also notice that the date format being set has the 'T' and Z characters in it. This tells the DateFormatter to include timeZone offset in hours. So, in the case of this post, the Z will represent -0500 at the end of the date format. Retrieves the date, in localized format. Description # Description. This is a newer function, intended to replace datei18n without legacy quirks in it. Note that, unlike datei18n, this function accepts a true Unix timestamp, not summed with timezone offset.
★★ Star me to follow the project! ★★ Created and maintaned by Daniele Margutti - www.danielemargutti.com. SwiftDate is the definitive toolchain to manipulate and display dates and time zones on all Apple platform and even on Linux and Swift Server Side frameworks like Vapor or Kitura.
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. Bloom 1 0 467 download free.
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.
A locale on the Windows platform is a combination of a language and optionally the country or region the language is spoken it. The local name is the two-letter abbreviation of the language in lower case optionally followed by a hyphen and the two-letter abbreviation of the country in upper case. The locale name for English as used in the United States is “en-US”, the locale for German as used in Austria is “de-AT”, a country-neutral English is just “en”.
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 https://goosingle.weebly.com/textual-7-lightweight-irc-client-v7-1-3.html. 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 Vlc media player 3 0 8 64 bit download. Microsoft office 365 mac app store price. -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.
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.
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.
0 
Star