MSACCESS VBA Date Formatting and regional settings conflict

Posted on 2012-09-04
Medium Priority
Last Modified: 2012-09-16
The following issue has arisen for formatting a 'Date String' where the users regional settings are not consistent with the output format required.  

We have developed some VBA module code in MSACCESS to accept a 'date' and to format an output string that can be used in SQL syntrax WHERE clause.  The format we require in all circumstances is "dd-mmm-yyyy".  This would be delimited by a ' or # depending on whether the command was issued locally in MSACCESS on linked tables or as a passthrough to SQL server.

Code snippets and examples of problem below:-

Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
      pBuildSQLWhereFormat = "'" & Format(DateValue(pvarFldValue), "dd-mmm-yyyy") & "'"
End Function

For regional settings English (Australia) this would work as follows:-


For regional settings for French this works as follows:-


For regional settings for Indonesia this works as follows:-


Both the 'French' and 'Indonesia' regional settings do not return the required format and result in subsequent SQL statements failing as follows:-

Syntax error in date in query expression '[Week End Date] >= #31-août-2012'.

What is the process for managing different regional settings for date formatting where we want to have the return format as "dd-mmm-yyyy'.

Question by:wghayes
  • 2
LVL 40

Expert Comment

ID: 38366673
I don't understand why you like to have month as name, but you can convert all to english:
Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
Dim M As Variant, D As Date
D = DateValue(pvarFldValue)
M = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
      pBuildSQLWhereFormat = "'" & Format(D, "dd-") & M(Month(D) - 1) & Format(D, "-yyyy") & "'"
End Function

Open in new window

LVL 52

Expert Comment

by:Gustav Brock
ID: 38366742
This is a known trap and theres is no easy way around the localized month names.

Your best option is to redesign your function like this:

Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
      pBuildSQLWhereFormat = "'" & Format(DateValue(pvarFldValue), "yyyy\/mm\/dd") & "'"
End Function

Also, this function will fail for many non-date parameter values including Null, thus:

Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
    If Not IsDate(pvarFldValue) Then
        ' For example, some default value.
        pvarFldValue = Date
    pBuildSQLWhereFormat = "'" & Format(DateValue(pvarFldValue), "yyyy\/mm\/dd") & "'"
End Function


Author Comment

ID: 38389272

According to ISO_8601 standard (http://en.wikipedia.org/wiki/ISO_8601) the accepted formats are either YYYY-MM-DD or YYYYMMDD.  The 1st variant uses - (dash) as seperator whereas you have elected to use / (slash).   What issues can I expect by using slash as seperator as opposed to dash? Are there localisation issues with a different date seperator that either a dash or slash overcome?  Can you comment?

Using dash I have function as:-

dteDateValue = DateValue(pvarFldValue)
pBuildSQLWhereFormat = "'" & Format(dteDateValue, "yyyy-") & Format(dteDateValue, "mm-") & Format(dteDateValue, "dd") & "'"

Format works in VBA, JET/ACE and MS SQL.

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 38389972
You can safely use Format(DateValue(pvarFldValue), "yyyy\-mm\-dd") & "'"

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question