Article ID: 142009 - View products that this article applies to.
This article was previously published under Q142009
Moderate: Requires basic macro, coding, and interoperability skills.
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.
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 FormatYou 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:
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.
Order Amount ------------ $5.47 $10.33
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 FormatThe 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:
(https://support.microsoft.com/kb/142055/EN-US/ )ACC: INTL Finnish Time Separator Period (.) Breaks DateValue()
Number FormatUse only English Number formats with Number functions in SQL. If you use non-English formats, some functions may fail. For example:
Note the comma (,) instead of a period (.) as the Decimal separator. This SQL statement returns the following error:
SELECT CDbl(0,09) AS Field1 FROM Employees;
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.
Wrong number of arguments used with function in query expression 'CDbl(0,09)'.
For more information about the sort order, please see the following article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/133381/EN-US/ )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 display formats" using the Microsoft Access 97 Help Index.
Article ID: 142009 - Last Review: January 19, 2007 - Revision: 2.1
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.