ACC: How Windows Regional Settings Affect Microsoft Access (142009)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q142009
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article demonstrates how Microsoft Access handles Currency, Date/Time, and Number formats when you change the regional settings values in Control Panel.

NOTE: This article assumes that you are using Microsoft Access on a computer that is running a version of Microsoft Windows no earlier than Windows 95. The Regional settings/options and International settings in Windows 3.x or earlier are not identical to those in Windows 95, Windows 98, Windows NT, or Windows 2000.

MORE INFORMATION

NOTE: The names of options and settings in Windows 2000 may be slightly different than they are in Windows 95, Windows 98, and Windows NT. For example, in Windows 2000, the Regional Settings tool in Control Panel is called the Regional Options tool. This article uses the term "regional settings" to mean options that you set in either of these tools.

Currency Format

You set the way that Currency values are stored in Microsoft Access and the way that they are formatted on the Currency tab of the regional settings tool in Control Panel. Problems can occur if you change the regional settings after you have added data to a Currency field in a table. Changing the regional settings modifies only the data format; it does not change the actual data.

For example, if you select Dutch (standard) as the region on the Regional Settings tab (or Dutch (Netherlands) on the General tab in Windows 2000), a Currency field appears as follows: 
   Order Amount
   ------------
        fl 5,47
       fl 10,33

If you later change this setting to English (United States), the format of the field changes; however, existing data does not change. The existing data does not change because fl 5,47 does not buy the same amount as $5.47. For the English (United States) setting the same value would be formatted as follows: 
   Order Amount
   ------------
          $5.47
         $10.33
If you do not want the format to change when you change the regional settings, define a custom format for the Currency field in Microsoft Access, such as fl #,##, by using the field's Format property. The custom format in Microsoft Access overrides the Currency format set for regional settings in Control panel.

If you want Currency values to convert automatically based on the Currency format, you must create a custom Visual Basic for Applications procedure to provide that functionality. This article does not provide that procedure.

Date/Time Format

The Time tab of the regional settings tool determines the long and short formats of Date/Time values and Time separators. For example, the English (United States) Short Date format is M/D/YY and the French (Canadian) is YY-MM-DD. The English (United States) Time separator is a colon (:) and the Spanish (Chile) Time separator is a comma (,).



When you use a regional setting other than English (United States), consider the following:
  • In Visual Basic for Applications, you must use U.S./English Date formats when you create SQL statements.

    For more information about formatting dates regardless of regional settings, please see the following article in the Microsoft Knowledge Base:

    149095 ACC: How to Format Dates Regardless of Regional Settings

  • In the query design grid, you can use International Date formats.
  • The Data/Time values in the regional settings tool do not control the Medium Date format (MM-DDD-YY). The Medium Date format is determined by the language edition of Microsoft Access. For example, if you have a database on computer 1 that is running French Microsoft Access, and you then copy the database to computer 2 that is running English (US) Microsoft Access, the Medium Date format will be different on computer 2.
  • If you set the regional settings to Finnish and set your Time separator to a period (.), various Date and Time functions may not work.
For more information about issues using a period (.) as a time separator, please see the following article in the Microsoft Knowledge Base:

142055 ACC: INTL Finnish Time Separator Period (.) Breaks DateValue()

Number Format

Use only English Number formats with Number functions in SQL. If you use non-English formats, some functions may fail. For example: 
   SELECT CDbl(0,09) AS Field1 FROM Employees;
Note the comma (,) instead of a period (.) as the Decimal separator. This SQL statement returns the following error:
Wrong number of arguments used with function in query expression 'CDbl(0,09)'.
NOTE: When you change your regional settings, the Database Sort Order on the General tab of the Options dialog box on the Tools menu changes to match the country that you select. However, to update existing databases to the new sort order, you must run the Compact Database command on these databases.

For more information about the sort order, please see the following article in the Microsoft Knowledge Base:

133381 ACC: How the Windows Code Page Affects Sort Order

REFERENCES

For more information about international display formats, search for "international settings," and then "Controlling international data display formats" using the Microsoft Access 97 Help Index.
Modification Type:MajorLast Reviewed:5/7/2003
Keywords:kbenv kbinfo KB142009
©2002 Microsoft Corporation. All rights reserved.