Access: Windows Region Settings

Depending on the region you’re in, you might have particular expectations of how different types of data are displayed and stored. This can lead to confusion or complications. For example, the most popular format for dates around the world is dd/mm/yyyy, followed by dd.mm.yyyy and d.m.yyyy. In the United States, however, the typical convention for dates is mm/dd/yyyy. So imagine the potential for confusion between someone in Europe sending an email to a U.S. coworker about an event on 5/6/2018. The European is referring to June 5, 2018, but the American might interpret it as May 6, 2018.

To view or change your region settings in Windows, go to the Control Panel. Look for and select the option that includes the word “Region” or “Regional” in some form (it will vary depending on which version of Windows you have). There, you can set the country, language, and other information, such as number formats. Depending on the region you select, the default formats will vary. This will affect the defaults for how data is displayed in Access.

REGIONAL DIFFERENCES

Figure 1 shows the Short Date and Long Date format symbols for Windows as well as examples of short dates, long dates, currency, and long time values for a few regions. It also shows country and language abbreviations.

The format symbols used for Windows are similar to those used for Access, but there are a few differences. (Date and time format symbols for Access are discussed in the July 2018 column.) Windows uses “M” for month and “m” for minute, whereas Access uses “m” or “M” for month, and “N” or “n” for minute. “H” or “HH” means a 24-hour clock, “h” or “hh” is a 12-hour clock, and “tt” is used to denote an a.m./p.m. indicator for a 12-hour clock.

Looking at the figure, it’s interesting to note some of the quirks and differences. For example, the French part of Canada (CA-fr) uses a short date format more like the English part of Canada (CA-en) rather than the format used in France (FR-fr). Yet both Canadian formats use an unambiguous year-month-day format.

The differences between the two extend beyond the date format: CA-fr uses a 24-hour clock, not 12. Parentheses denote negative numbers instead of a minus sign. A space is added before the currency symbol because it comes after the value instead of before it. For long dates, the format is day-month-year instead of month-day-year. Leading zeros are displayed in time values. Instead of a comma, a semicolon is used to separate items in a list. And a space separates groups of thousands in a number, rather than a comma. But both CA-fr and CA-en use the metric measuring system.

DATA ISSUES

While the primary difference is how the data is displayed, region settings can create problems when working with data. Those who don’t use the U.S. formats, for example, might first experience problems with different formats when concatenating. For instance, when concatenating data in VBA or using SQL, the popular day-month-year formats aren’t recognized. There are only two formats that will always be understood—the U.S. format and the ODBC canonical format, which is yyyy-mm-dd hh:nn:ss (year-month-day hour:minute:second).

If your dates aren’t in the U.S. or canonical format when a date value is referenced, you must pass the data using values instead of the actual field. Using the Format function and adding in the delimiters will ensure that date values are handled properly, such as Format([MyDateField], "  # mm/dd/yyyy # " ).

For other data formats that might differ, such as phone numbers, you can store the data as a string and use an input mask to enter and display it in a particular format, such as (123) 456-7890.

SAMPLE DATABASE

This month’s download database (SF_1808_DATABASE_Windows-Region-Settings) contains a module called mod_ExcelSetting with a user-defined function (UDF) that uses Excel capabilities to read international settings for Windows.

The database also contains tables and other items using the various formats. Some of the data in the database tables was created by changing the region setting in Windows, rebooting the computer, reopening the database, and then running action queries. Records in FormatzDate and FormatzNumber were created using the Format function and named formats, such as Short Date and Long Date. Records in Settingz were read from Windows using a UDF that takes advantage of Excel's ability to get these settings. Note that running the Get_Excel_InternationalSetting function will also keep open a hidden instance of Excel that should be released when Access exits normally. Be sure to check out the database to see the various differences.

The list of countries was imported from a reference database available free on the AccessMVP website.

Lastly, I want to express thanks to Daniel Pineault, an Access expert living in the French part of Canada who writes applications that work across borders, for providing some of the information for this article. Daniel gets the values of international settings using a Windows API (Application Programming Interface) to read the registry, and he notes that Excel can enumerate this information, too. That’s great news because if Excel has something, Access can use it!

SF SAYS

The country and language specified in the Windows region settings affect how data is displayed.