CODEWITHSUNDEEP
vbadatetimems-access
Michael
Michael

Reputation: 3229

How to filter by date field when system date format is dd/mm/yyyy

I have a report which contains a datetime field. There is a form used to gather user input for the query, specifically the From and To date for the query to be filtered.

The report is opened using this code:

Dim str as String
str = "DateCreated BETWEEN #" & format(From_Date.value, "yyyy-mm-dd") & "# AND #" & DateAdd("d", 1, format(To_Date.value, "yyyy-mm-dd")) & "#"
DoCmd.OpenReport "report_name", acViewReport, WhereCondition:=str

On Windows machines that have the date format as mm/dd/yyyy, or yyyy-mm-dd, the query works.

Some users have the system date set to dd/mm/yyyy. The filter is reading 08/11/2021 (Nov 8th, 2021) as Aug 11th, 2021.

I tried formatting the date inputs for the WHERE condition using format(From_Date.value, "yyyy-mm-dd").

Does Access support dd/mm/yyyy format? How can I filter by a date range?

Is the only option to adjust the user's system date format?

Upvotes: 0

Views: 872

Answers (2)

Erik A
Erik A

Reputation: 32642

Always use parameters, never concatenate formatted dates to SQL strings.

If you use formatted dates in SQL strings you're setting yourself up for failure, the chance of accidentally doing something wrong is huge.

Dim str as String
str = "DateCreated BETWEEN p1 AND p2"
DoCmd.SetParameter "p1", From_Date.value
DoCmd.SetParameter "p2", DateAdd("d", 1, To_Date.value)
DoCmd.OpenReport "report_name", acViewReport, WhereCondition:=str

Read more on parameters in Access: How do I use parameters in VBA in the different contexts in Microsoft Access?

Upvotes: 3

John Wu
John Wu

Reputation: 52210

You can use one of the general date format codes (instead of a custom format string) to use the system format.

Dim str as String
str = "DateCreated BETWEEN #" & format(From_Date.value, "Long Date") & "# AND #" & DateAdd("d", 1, format(To_Date.value, "Long Date")) & "#"
DoCmd.OpenReport "report_name", acViewReport, WhereCondition:=str

Upvotes: 0

Related Questions