ACC: How to Format Dates As U.S. Regardless of Regional Settings

ID: Q149095

The information in this article applies to:

Microsoft Access 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use Visual Basic for Applications to concatenate dates into a SQL string, you must use a standard US date format, regardless of the Regional Settings in Control Panel. This article shows you how to create a custom function to convert any date into a U.S. date format.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

To format dates, regardless of regional settings, create a module and type the following procedure:

   Function MakeUSDate(x As Variant)
      If Not IsDate(x) Then Exit Function
      MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
   End Function

To test this procedure:

1. Open Control Panel.

2. Open Regional Settings, select English (British) as the Regional

   setting, and click OK. Restart your computer for the setting to take
   effect.

3. Start Microsoft Access and open the sample database Northwind.mdb (or

   Nwind.mdb in Microsoft Access 2.0).

4. Create a new module and add the MakeUSDate()function that you created.

5. Create a new query in Design view and add the Orders table.

6. In the query grid, add the following fields:

      Field: OrderDate
         Sort: Ascending
         Show: Yes
      Field: Expr1: MakeUSDate([OrderDate])
         Show: Yes

   NOTE: [OrderDate] is [Order Date] in Microsoft Access 2.0

7. Run the query. Note that the OrderDate column is in a dd/MM/yy format

   and the Expr1 column is in a #m/d/yyyy# format.

After testing this, reset the Regional Settings for your computer to English (United States), and restart your computer for the setting to take effect.

Note: For the current date and time only:

The Date$ function returns the current date in US format (mm-dd-yyyy) regardless of the regional settings. The Date function, however, returns a variant number, which when converted to a string, is formatted according to regional settings.

Similarly, the Time$ function returns the current time formatted as "hh:nn:ss" on a 24-hour clock regardless of regional settings. The Time function, however, returns a variant number, which when converted to a string is formatted according to regional settings.

REFERENCES

For more information about concatenating SQL strings, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q136059
   TITLE     : ACC: Errors Concatenating Variables or Controls (2.0/7.0/97)

Additional query words: how to INTL

Keywords          : kbusage 
Version           : WINDOWS:2.0,7.0,97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: February 26, 1999