db.zip
There you go
db.zip
There you go
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"
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
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?
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 ??Code: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];
OK, found it in the other query.
Code: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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
looks like your form is corrupted. This works for me
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 wellCode: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;
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
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
using a date of 13/9/2023 I got around 10. But now deleted all from my pc so can't verify
think the OP only responds to the post they get advised about, doesn't see the rest
I get 13, however I removed the group by.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.