MSACCESS VBA Date Formatting and regional settings conflict

Posted on 2012-09-04
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
    LVL 39

    Expert Comment

    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 48

    Expert Comment

    by:Gustav Brock
    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


    According to ISO_8601 standard ( 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 48

    Accepted Solution

    You can safely use Format(DateValue(pvarFldValue), "yyyy\-mm\-dd") & "'"

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now