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.
: 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.
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 Currencyformat, you must create a custom Visual Basic for Applications procedure toprovide that functionality. This article does not provide that procedure.
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:
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()
Use only English Number formats with Number functions in SQL. If you usenon-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. ThisSQL 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 onthe 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 articlein the Microsoft Knowledge Base:133381
ACC: How the Windows Code Page Affects Sort Order
For more information about international display formats, search for"international settings," and then "Controlling international data displayformats" using the Microsoft Access 97 Help Index.