ACC: How to Format Any Date As U.S. Date Regardless of Regional Settings (149095)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q149095

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:

136059 ACC: Errors Concatenating Variables or Controls (2.0/7.0/97)

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming kbusage KB149095
©2004 Microsoft Corporation. All rights reserved.