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

[Bilakos93]

db.zip
There you go

[mike60smart]

Hi

Well yet again your relationships are all wrong (see attached)

1. All tables should have a PK - Autonumber with a name other than just "ID"
2. Your table "tblStudents" has a primary key set on the field "agm" which has a DataType of Number set as Double
3. Your table "tblStuDiag" has a PK ste as "ID" whcih should be named "StuDiagID"
4. Your table "tblStuDiag" has a field named "Student" which is set as a Lookup Field. You need to Google "The Evils of Lookup Fields in Access Tables"
5. Your table "tblStuDiag" has field named "Diagnosis" with a DataType of Text, which is set as a Lookup Field. You need to Google "The Evils of Lookup Fields in Access Tables"

[Welshgasman]

All that is moot ATM?
I would just get the query working before you start grouping, small steps

However I cannot get the query to recognise your report date for either criteria?
Edt: That was likely due to that other query perhaps?

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

I honestly do not know what you have done, as I created a new form with a date control and referred to that, removed the formcontrol you had as a table field, and yet it still prompts for reportdate!rdate ??
OK, found it in the other query.

SELECT students_rep.lname, students_rep.fname, students_rep.agm, tblStudDiag.diagnosis.Value, tblStudDiag.duration, DateAdd("d",duration-1,StartDate) AS EndDate, students_rep.nd_dsls & " " & students_rep.taksi AS Class,  DateAdd("d",[duration]-1,[StartDate]) AS Expr1
FROM students_rep INNER JOIN tblStudDiag ON students_rep.agm=tblStudDiag.student
WHERE tblStudDiag.StartDate<=[Forms]![form1]![rdate];

I get two records with a date of 12/09/2023
No idea if that is correct or not, even with the AND ?
AGM 00171 and 03877 are the two records produced, but query is slow as hell due to that first query?, even on an SSD.

[CJ_London]

looks like your form is corrupted. This works for me

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]![form1]![rdate] AS reportdate
FROM students_rep INNER JOIN tblStudDiag ON students_rep.agm = tblStudDiag.student
WHERE (((CDate([forms]![form1]![rdate])) Between [StartDate] And DateAdd("d",[duration]-1,[startdate])))
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, [forms]![form1]![rdate], DateAdd("d",[tblStudDiag].[duration]-1,[tblStudDiag].[startdate]), forms!form1!rdate;

I created a new form (called Form1) and populated with a single control called rDate - also had to modify your other query to use the new form as well

[Bilakos93]

dailyreportdate!rdate is also used for the query students_rep , that's probably why

All the entries are from 12th Sep onwards , so not sure, I'll have a look in a moment

[CJ_London]

If you are responding to me, read the whole post

[Welshgasman]

If you are responding to me, read the whole post

Probably me CJ, as it took me a while to find the prompt reason.
However when I use my Form1 in both queries, I do not get the prompt and the query runs presenting two entries.

I got rid of all the Group By to just make a simple select.

[CJ_London]

using a date of 13/9/2023 I got around 10. But now deleted all from my pc so can't verify

[CJ_London]

think the OP only responds to the post they get advised about, doesn't see the rest

[Welshgasman]

using a date of 13/9/2023 I got around 10. But now deleted all from my pc so can't verify

I get 13, however I removed the group by.

[Bulzie]

To Troubleshoot: Take out the first part of the Where "[tblStudDiag].[StartDate]<=forms!dailyreportdate!rdate and" and see if it works with the last part. Also can test with replacing [tblStudDiag.duration]-1 with an actual number and see if it works, etc. Replace the !rDate with actual date and see if it works. Do all these one at a time to pinpoint where the issue is.