MS Access report not able to filter by date field when system date format is dd/mm/yyyy-CodePudding

In Access, 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 by.

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 just fine. There are some users however who have the system date set to dd/mm/yyyy, and Access can't seem to recognize it. So when opens the report and applies the filter, it is reading 08/11/2021 (Nov 8th, 2021) as Aug 11th, 2021, which obviously gives the wrong results.

I have tried formatting the date inputs for the WHERE condition using the following:

Using the code format(From_Date.value, "yyyy-mm-dd") to format the date was a suggested solution for this, but that is not working.

Why does Access not support dd/mm/yyyy format? If it doesn't then how can I properly filter by a date range?

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

Thanks

CodePudding user response：

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

CodePudding user response：

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?