Query supposed to return values where a date is between two dates but not doing it!

[Bilakos93]

Hi everyone



The below code returns the records that have StartDate<reportdate but not those have EndDate>reportdate. It's like it ignores the second part of the WHERE.

SELECT students_rep.[lname], students_rep.[fname], students_rep.agm, [tblStudDiag].[diagnosis].Value, [tblStudDiag].[StartDate], [forms]![dailyreportdate]![rdate] AS reportdate, [tblStudDiag].duration, dateadd("d",[duration]-1,[StartDate]) AS EndDate, students_rep.nd_dsls & " " & students_rep.[taksi] AS Class
FROM students_rep INNER JOIN tblStudDiag ON students_rep.agm=[tblStudDiag].[student]
WHERE ((([tblStudDiag].[StartDate])<=forms!dailyreportdate!rdate) and ((dateadd("d",[tblStudDiag.duration]-1,[tblStudDiag.startdate]))>=forms!dailyreportdate!rdate));

"agm" is the primary key of the query students_rep




Any smart ideas??

Many thanks!

[Welshgasman]

Access always puts in too many brackets for my liking.
I would exect the WHERE clause to be enclosed by brackets though, if only to make it obvious to me?
So I reckon you are missing a closing bracket at the end of the SQL ?

Perhaps try

SELECT students_rep.[lname], students_rep.[fname], students_rep.agm, [tblStudDiag].[diagnosis].Value, [tblStudDiag].[StartDate], [forms]![dailyreportdate]![rdate] AS reportdate, [tblStudDiag].duration, dateadd("d",[duration]-1,[StartDate]) AS EndDate, students_rep.nd_dsls & " " & students_rep.[taksi] AS Class
FROM students_rep INNER JOIN tblStudDiag ON students_rep.agm=[tblStudDiag].[student]
WHERE ([tblStudDiag].[StartDate]<=forms!dailyreportdate!rdate and dateadd("d",[tblStudDiag.duration]-1,[tblStudDiag.startdate]) >=forms!dailyreportdate!rdate);

or even

SELECT students_rep.[lname], students_rep.[fname], students_rep.agm, [tblStudDiag].[diagnosis].Value, [tblStudDiag].[StartDate], [forms]![dailyreportdate]![rdate] AS reportdate, [tblStudDiag].duration, dateadd("d",[duration]-1,[StartDate]) AS EndDate, students_rep.nd_dsls & " " & students_rep.[taksi] AS Class
FROM students_rep INNER JOIN tblStudDiag ON students_rep.agm=[tblStudDiag].[student]
WHERE [tblStudDiag].[StartDate]<=forms!dailyreportdate!rdate and dateadd("d",[tblStudDiag.duration]-1,[tblStudDiag.startdate]) >=forms!dailyreportdate!rdate;

[Bilakos93]

I couldn't agree more!
SQL is usually easy to read but access makes things complicated with no reason

unfortunately neither of those did the job..

[Welshgasman]

You don't have a time element in those dates, do you?

[Bilakos93]

I'm guessing no?
How can I make sure?

[CJ_London]

also this part of your query will return unexpected results

......[tblStudDiag].[StartDate])<=forms!dailyreportdate!rdate......

the rDate control is a string and will interprete any ambiguous values as the US format of mm/dd/yyyy

a date of 19/09/2023 is unambiguous since there are not 19 months in the year.

But a date of 12/09/2023 will be interpreted as 9th December, not 12th September

a date of 10/10/2023 will work either way, but 11/10/2023 will be interpreted as 10th November. not 11th October

use the # date delimiters and the format function - recommend use the SQL standard of yyyy-mm-dd

Also don't recommend using the shortened date (23 v 2023) as that may also be interpreted differently

try

......[tblStudDiag].[StartDate])<='#' & format(forms!dailyreportdate!rdate,'yyyy-mm-dd') & '#'......

[Bulzie]

Could be something in your join. Also where is this value forms!dailyreportdate!rdate? Is that in the header on the form showing these records? Maybe code not recognizing that date?

Also try to troubleshoot specific parts like replace some of that with actual dates, take out forms!dailyreportdate!rdate and put in an actual date there to see if it works, etc.

[Bilakos93]

The problem seems to be here
((dateadd("d",[tblStudDiag.duration]-1,[tblStudDiag.startdate]))>=forms!dailyreportdate!rdate))
As you can see, access does return the entries for which startdate<=reportdate but ignores the "enddate>=reportdate" part.
The value [forms]![dailyreportdate]![rdate] is in the detail section of the form. Access doesn't recognise it for some reason, I have to enter it manually from a message box...

[CJ_London]

Shame you didn’t mention that at the beginning, could have been answered in the first response

You are missing square brackets around the .

or Remove them - they are only required when name has spaces, non alpha numeric characters or is a reserved word

[Bilakos93]

Which ones are you referring to?

[Bilakos93]

I actually removed all the square brackets and now the code is

SELECT students_rep.lname, students_rep.fname, students_rep.agm, tblStudDiag.diagnosis.Value, tblStudDiag.StartDate, tblStudDiag.duration, DateAdd("d",duration-1,StartDate) AS EndDate, students_rep.nd_dsls & " " & students_rep.taksi AS Class, forms!dailyreportdate!rdate AS reportdateFROM students_rep INNER JOIN tblStudDiag ON students_rep.agm = tblStudDiag.student
GROUP BY students_rep.lname, students_rep.fname, students_rep.agm, tblStudDiag.diagnosis.Value, tblStudDiag.StartDate, tblStudDiag.duration, DateAdd("d",duration-1,StartDate), students_rep.nd_dsls & " " & students_rep.taksi, DateAdd("d",tblStudDiag.duration-1,tblStudDiag.startdate), forms!dailyreportdate!rdate
HAVING (((tblStudDiag.StartDate)<=forms!dailyreportdate!rdate) And (DateAdd("d",duration-1,tblstuddiag.StartDate))>=forms!dailyreportdate!rdate);

But still, the query won't recognise the value from the form and it will return the same results (ignoring the reportdate<=enddate)

[Bilakos93]

Interestingly when I write OR instead of AND I get all the entries , which means that there is definitely something wrong on the (DateAdd("d",duration-1,tblstuddiag.StartDate))>=forms!dailyreportdate!r date) I'm guessing?

[CJ_London]

have you checked out post #6 ?

[mike60smart]

Can you upload the Db?

[Welshgasman]

Interestingly when I write OR instead of AND I get all the entries , which means that there is definitely something wrong on the (DateAdd("d",duration-1,tblstuddiag.StartDate))>=forms!dailyreportdate!r date) I'm guessing?

Not really, completely different criteria.