Microsoft KB Archive/210069

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

Article ID: 210069

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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:  Click Start, point to Settings, and then click Control Panel. 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. Start Microsoft Access and open the sample database Northwind.mdb.  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  Create a new query in Design view and add the Orders table.  In the query grid, add the following fields: <pre class="fixed_text">  Field: OrderDate Sort: Ascending Show: Yes

Field: Expr1: MakeUSDate([OrderDate]) Show: Yes </li> 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.</li></ol>

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.

<div class="references_section">