MSACCESS Form VBA Open Where Clause - US v AU Date Formatting

Question

Morning all, This should be a simple fix and I have searched around and found things that appear they should work, but they don't seem to though...

So - it's a simple "Open this form and show only records from This date" function and, you guessed it, good ol' US of A date formatting is the problem and no matter which way I try it nothing is solving the problem...

So - here's the form code...

DoCmd.OpenForm "frm_Prod_Runs_Edit_List", , , "# " & NEWDATE & " #"

to find NEWDATE...

I strip the date components from a couple of combo boxes (CBOYear & CBOMonth) and a field that represents the day of the month (data will be in the format "1st" or "2nd" etc...)

I concatenate the individual components...

NEWDATE = DateSerial(TempYear, TempMonth, TempDate)

A msgbox NEWDATE popup results in the correct data (ie 1/3/18)

But when I run the code it either selects everything or nothing... the only time the function works is when the day field is 13 or higher (ie 13/3/18) - thus it can determine the correct format to work with.

I'm sure the correct answer is already here somewhere - the correct question however may be something I have not thought to search for.

I have tried to use DATEVALUE() before and within the form open code. I have tried to hard code the US format to see if that works...

DoCmd.OpenForm "frm_Prod_Runs_Edit_List", , , "#3/1/18#"

No good... shows all records - same if I hard code for the AU format #1/3/18#

My PC is set up for AU format dd/mm/yy

So... Which function do I need to use to convert the AU Date string into US Date string prior to calling the Openform, so the correct records open? OR is there a better way to write the WHERE clause in the Docmd line to achieve this result? - Or do I need to reset all my date fields to US format and convert them back to AU on the forms only?

Thanks for the feedback...

score 1 · Answer 1 · answered Mar 28 '18 at 00:38

1

In VBA and VB6, date-literals using the # syntax are always in M/dd/yyyy format, regardless of the user's date format settings. This is a legacy of VBA/VB6's development in the USA before localization was a concern (and the USA is the only country to use the illogical MM/dd/yyyy format).

Note it's M/dd/yyyy and not MM/dd/yyyy - so omit any leading zeroes.

This is documented here: https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/date-data-type

The same rule also applies to date-literals in Access SQL (not the same thing as VBA/VB6). Fortunately in SQL Server you always use the ISO 8601 yyyy-MM-dd format in quotes... shame about Access though.

answered Mar 28 '18 at 00:38

Dai

141,631
28
261
374

Sorry no leading "0" 's - I tried the Workaround format suggestion in the link - I formatted it for both US and AU format to no avail... all records are opening... Any other options or suggestions? – Dude Mar 28 '18 at 01:41
@Dude Can you post your entire function/sub verbatim? Including any global variables and module-wide variables? I need to see the full picture. – Dai Mar 28 '18 at 01:45
prev comment failed due to code too long... - can I upload a text file to this site or send to you? not sure how this works – Dude Mar 28 '18 at 02:52
@Dude Edit your existing Question and put your code in there instead of in a comment. – Dai Mar 28 '18 at 03:11

Erik A · Answer 2 · 2018-03-28T06:22:27.247

Your first problem is that you're not comparing NewDate to something.

Your second problem is that you're using string concatenation to filter by a date, and that results in the wrong date.

You can view this answer for the different ways to use parameters in Access.

Your final code should probably look something like this:

NEWDATE = DateSerial(TempYear, TempMonth, TempDate)
DoCmd.SetParameter("NewDateParam", NEWDATE)
DoCmd.OpenForm "frm_Prod_Runs_Edit_List", , , "SomeDateField = NewDateParam"

Or, if there might be time stored with the date:

NEWDATE = DateSerial(TempYear, TempMonth, TempDate)
DoCmd.SetParameter("NewDateParam", NEWDATE)
DoCmd.OpenForm "frm_Prod_Runs_Edit_List", , , "SomeDateField - NewDateParam < 1"

score 0 · Accepted Answer · answered Mar 28 '18 at 07:44

You simply need a properly formatted string expression for your date value - and the correct syntax:

Dim NEWDATE As Date
Dim WhereCondition As String

NEWDATE = DateSerial(TempYear, TempMonth, TempDate)
WhereCondition = "[NameOfYourDateField] = #" & Format(NEWDATE, "yyyy\/mm\/dd") & "#"

DoCmd.OpenForm "frm_Prod_Runs_Edit_List", , , WhereCondition

MSACCESS Form VBA Open Where Clause - US v AU Date Formatting

3 Answers3