ACC2000: How to Format Dates as U.S. Dates Regardless of Regional Settings

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To format dates regardless of regional settings, you can create a Visual Basic for Applications function. To do so, follow these steps:

1. Click Start, point to Settings, and then click Control Panel.
2. Open the Regional Settings tool, select English (United Kingdom) 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.
4. Create a new module, and type or paste the following function:

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

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
   					

7. Run the query. Note that the OrderDate column is in a dd/mmm/yy format and the Expr1 column is in a #m/d/yyyy# format.

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

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