ACC: How Windows Regional Settings Affect Microsoft Access

ID: Q142009

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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 in the Windows 95 or later, and Windows NT 4.0 Control Panel.

NOTE: This article assumes you're using Microsoft Access with Windows 95 or later, or Windows NT 4.0. The Regional and International settings in earlier versions of Microsoft Windows are not identical to those in Windows 95 and later, and Windows NT 4.0.

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 Regional Settings in the Windows 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 tab in Regional Settings to Dutch (standard), a currency field appears as follows:


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

If you later change the Regional Settings tab in 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 kr 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 do not want the format to change when the Regional Settings are changed, define a custom format for the Currency field in Microsoft Access, such as kr #,##, using the field's Format property. The custom format in Microsoft Access overrides the Currency format in the Control Panel's Regional Settings.

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 Regional Settings 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. The English (United States) Time separator is a colon (:) and the Spanish (Chile) Time separator is a comma (,).

When you use Regional Settings other than English (United States), consider the following:

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

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

Q149095 ACC: How to Format Dates Regardless of Regional Settings

In the query grid, you can use International Date formats.

Regional Settings doesn't 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, running French Microsoft Access, and then copy the database to computer #2, running English (US) Microsoft Access, the Medium Date format will be different on computer #2.

If you set the Regional Settings tab in 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:

Q142055 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;

Notice 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:

Q133381 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.

Additional query words: globalization international int'l localization localisation


Keywords          : kbenv 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: April 14, 1999