ACC2000: How Windows Regional Settings Affect Microsoft Access

ID: Q208596

The information in this article applies to:

Microsoft Access 2000

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

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 in Control Panel in Windows 95/98 or Windows NT 4.0.

NOTE: This article assumes that you are using Microsoft Access with Windows 95/98 or Windows NT 4.0. The regional and international settings in earlier versions of Microsoft Windows are not identical to those.

MORE INFORMATION

Currency Format

In Microsoft Access, the way currency values are stored and the way they are formatted is set on the Currency tab of the Regional Settings tool in Control Panel. Problems can occur if you change the regional settings after adding 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 set the regional settings in the Regional Settings tool to Dutch (Standard), a Currency field appears as follows:


   Order Amount
   ------------
        fl 5,47
       fl 10,33

If you later change the regional settings 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) regional setting, the same value would be formatted like this:


   Order Amount
   ------------
          $5.47
         $10.33

If you want the format to remain unchanged 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 Access overrides the Currency format in the Regional Settings tool 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 in 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 French (Canadian) is YY-MM-DD.

When you use regional settings other than English (United States), consider the following points:

In Visual Basic for Applications, you must use U.S./English Date formats when creating SQL statements.

For additional information about formatting dates regardless of regional settings, please see the following article in the Microsoft Knowledge Base:
Q210069 ACC2000: How to Format Dates as U.S. Dates Regardless of Regional Settings
In the query grid, you can use International Date formats.
Regional settings do not control the Medium Date format (MM-DDD-YY). The Medium Date format is determined by the language that you are using in Access. For example, if you have a database on computer #1 running French Microsoft Access, and then copy the database to computer #2 running English (U.S.) Microsoft Access, the Medium Date format is different on computer #2.

Number Format

Use only English Number formats with Number functions in SQL. If you use non-English formats, some functions may not work. 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.

REFERENCES

For more information about the sort order, click Microsoft Access Help on the Help menu, type international settings in the Office Assistant or the Answer Wizard, and then click Search to view "Controlling international data display formats."

Additional query words: globalization international int'l localization localisation

Keywords : kbenv kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo