ACC: How to Format Dates Regardless of Regional Settings

• Microsoft Access 2.0, 7.0, 97

SUMMARY

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

When you concatenate dates into a SQL string using Visual Basic for Applications, you must use a standard US date format regardless of the Control Panel's Regional Settings on your computer. This article provides a custom function that you can use to convert any date into a US 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

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.

   Open Regional Settings, select English (United States) as the Regional
   setting, and click OK. Restart your computer for the setting to take
   effect.

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, 95)