ACC: How Windows Regional Settings Affect Microsoft Access

• 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 and Windows NT 4.0 Control Panel.

NOTE: This article assumes you're using Microsoft Access with Windows 95 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 Windows NT 4.0.

MORE INFORMATION

Currency Format

In Microsoft Access, the way Currency values are stored and the way they are formatted derives from 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

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

        ARTICLE-ID: Q149095
        TITLE     : ACC: How to Format Dates Regardless of Regional Settings
  
  

• In the query grid, you can use International Date formats.
• Regional settings don'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.

   ARTICLE-ID: Q142055
   TITLE     : 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;

   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:

   ARTICLE-ID: Q133381
   TITLE     : 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.