Microsoft KB Archive/142009

= ACC: How Windows Regional Settings Affect Microsoft Access =

Article ID: 142009

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



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: <pre class="fixed_text">  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

<div class="references_section">