Access SQL Date condition not working properly

Question

I'm trying to retrieve records between two specific dates. The dates in the table from which I'm retrieving the records are expressed in the "dd/mm/yyyy" format, and the condition I'm using is expressed the same.

I've noticed that my code doesn't work as expected, and I discovered that its due to the fact that it reads the date as in US Format rather than in the EU Format. For instance:

SELECT CodOpe, PV_Sensy, Cashflow_Date FROM Amort_Plans_Calc 
WHERE Cashflow_Date > #01/04/2018#  AND Cashflow_Date <= #01/10/2021#
AND CodOpe = "06340CMLIM00506007228378601";

returns the wrong number of records. If I use this code, instead, it returns the right number:

SELECT CodOpe, PV_Sensy, Cashflow_Date FROM Amort_Plans_Calc 
WHERE Cashflow_Date > #04/01/2018#  AND Cashflow_Date <= #10/01/2021#
AND CodOpe = "06340CMLIM00506007228378601";

I already set all fields in the right format. I've tried using the Format function within the query condition:

SELECT CodOpe, PV_Sensy, Cashflow_Date FROM Amort_Plans_Calc 
WHERE Cashflow_Date > Format("01/04/2018", "dd/mm/yyyy") 
AND Cashflow_Date <= Format("10/01/2021", "dd/mm/yyyy") 
AND CodOpe = "06340CMLIM00506007228378601";

This method didn't worked neither (it didn't show any result at all).

Consider, also, that I just put it simplier that it actually is, since my real script needs the Dateadd function in the condition (hence my need to have Access correctly read the dates) and should be something like this:

SELECT CodOpe, PV_Sensy, Cashflow_Date FROM Amort_Plans_Calc 
WHERE Cashflow_Date > Dateadd("m", 18, Format("01/04/2018", "dd/mm/yyyy")) 
AND Cashflow_Date <= Dateadd("y", 5, Format("10/01/2021", "dd/mm/yyyy"))
AND CodOpe = "06340CMLIM00506007228378601";

This version doesn't show any result, but it seems I can't put the hashtags outside the dateadd function.

How can I solve this problem?

Thanks in advance,

Jona

Answer 1

Assuming this is a date column?

Your query has to be in the USA format. Mm/dd/yyyy.

So ANY sql string you create will have to use the above format.

However, it is RATHER likely that you receiving input from a user. In that case, the format of the text box MUST be set to date, and thus the text box will take on the data type of data. In this case, you again use format().

All dates are “internal” format. You can display them anyway you want in reports etc. However, if you build the SQL string by hand, or in code, then you MUST use the usa format.

You can’t use Format("01/04/2018", "dd/mm/yyyy"), as the date you are supplying is a STRING, and NOT a date type.

Given your regional settings, then format(#01/04/2018#,"mm/dd/yyyy") should work, but that assumes your regional settings are dd/mm/yyyy.

So you can (and should) apply the format setting (usa) to any date type you supply. No need to bother with format when you can just use #04/01/2018#.

You don’t explain how you are supplying the date(s) (form, code, direct edit of sql etc.).

thus You need dateadd("m", 18, #04/01/2018#)

You can also likely use:

Dateadd("m", 18, format(#04/01/2018#,"mm/dd/yyyy"))

So either dump the use of format, or ensure that you supply a DATE type (value surrounded with #). You THEN convert that format to mm/dd/yyyy.

Answer 2

I've just encountered another problem, related to the above described issue. This time the problem was that I have to Join two tables on two date fields both expressed in the EU format:

SELECT A.Cashflow_Date, B.Calendar_Day FROM Amort_Plans_Calc A
INNER JOIN BD_Calendar B
ON A.Cashflow_Date = B.Calendar_Day;

This gave no results, even though there are lots of records with the same date that should appear. I've found, however, a workaround that really seems to work:

SELECT A.Cashflow_Date, B.Calendar_Day FROM Amort_Plans_Calc A
INNER JOIN BD_Calendar B
ON DateValue(A.Cashflow_Date) = DateValue(B.Calendar_Day);

This returned the correct number of records. What do you think about it?