Microsoft KB Archive/149095

= ACC: How to Format Any Date As U.S. Date Regardless of Regional Settings =

Article ID: 149095

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



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:

 Open Control Panel. Open Regional Settings, select English (British) as the Regional setting, and click OK. Restart your computer for the setting to take effect. Start Microsoft Access and open the sample database Northwind.mdb (or Nwind.mdb in Microsoft Access 2.0). Create a new module and add the MakeUSDatefunction that you created. Create a new query in Design view and add the Orders table. 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 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.</li></ol>

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.

<div class="references_section">