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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210069
Advanced: Requires expert coding, interoperability, and multiuser skills.

SUMMARY

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.

MORE INFORMATION

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.

REFERENCES

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

210244 ACC2000: Errors Concatenating Variables or Controls

Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto kbusage KB210069
©2004 Microsoft Corporation. All rights reserved.