How to format date in a SQL query?

Question

Hello I'm using MS JET OLEDB 4.0 on VBA (MS ACCESS)

SQL = "SELECT COUNT(Date) FROM [Orders] WHERE [Orders].[Date] BETWEEN " & "#" & StartDate & "#" & " AND " & "#" & EndDate & "#"

The [Date] is formatted like f dd/mm/yyyy (French) and the StartDate and EndDate are formatted like mm/dd/yyyy (Two date objects).

My belief is that BETWEEN only compares dates formatted mm/dd/yyyy, so is there a way like a function to convert the [Date] formatting into mm/dd/yyyy so the between function can compare correctly?

Edit: Using a string instead of the dates like follow:

StartDateFormatted = Format(StartDate,"dd/mm/yyyy")
EndDateFormatted = Format(EndDate,"dd/mm/yyyy")

So as to be sure of the startdate and enddate format. It doesn't work still.

I'm left to assume two things:

• Either BETWEEN onlys compares mm/dd/yyyy formats

• I have to use a function to format [Date] to mm/dd/yyyy

Thanks to @Maciej Los for answering my question.

Answer 1

I have noticed from the SQL compilation of the query when using Between, that it turns it to MM/DD/YYYY and it did not work till I used Format(startdate, "mm/dd/yyyy"). Please check the snapshop.

Answer 2

Use Format function.

SQL = "SELECT COUNT(Date)" & vbcr & _
"FROM [Orders]" & vbcr & _
"WHERE [Orders].[Date] BETWEEN #Format(" & StartDate & "], 'MM/dd/yyyy')# AND #Format(" & EndDate & ",'MM/dd/yyyy')#"

Answer 3

Always use ISO date formatting YYYY-MM-DD and you will have no problems. Without formatting.