MSACCESS VBA Date Formatting and regional settings conflict
Posted on 2012-09-04
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") & "'"
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'.