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

• Microsoft Access 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.

REFERENCES

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

Q210244 ACC2000: Errors Concatenating Variables or Controls

Additional query words: how to INTL

Keywords : kbusage kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto