Query - Date criteria problem

[zan]

Hey guys, I really need some help on this problem.

I have a database for a tutorial center, and we have invoices recording the dates of the lessons that students pay for in this month.

We have a reminder form that display query results when today is the last lesson for the student so we can give them a new invoice. This has been working perfectly well untill now, it suddenly no longer displays the student and invoice for their last lesson.

What happens is, if there are 4 last lessons on Sept. 17, it displays reminders for 4 students in a datasheet subform. However, Sept. 17 may only be the second last or third lesson for some of the students. It just randomly shows a student that has a lesson on Sept.17, not their last lesson.

Thanks ahead to anyone who can offer me any advice.

SELECT tblStudents.StudentID, tblStudents.StudentName, Last(tblDate.dtDate) AS DateOfLast, tblInvoice.InvoiceID, tblInvoice.Paid, tblCourse.ShortDescription
FROM tblStudents INNER JOIN (tblInvoice INNER JOIN ((tblCourse INNER JOIN tblInvoiceDetail ON tblCourse.CourseCode = tblInvoiceDetail.CourseCode) INNER JOIN (tblDate INNER JOIN tblInvoiceDetail2 ON tblDate.DateID = tblInvoiceDetail2.DateID) ON tblInvoiceDetail.InvoiceDetailID = tblInvoiceDetail2.InvoiceDetailFK) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID) ON tblStudents.StudentID = tblInvoice.StudentFK
GROUP BY tblStudents.StudentID, tblStudents.StudentName, tblInvoice.InvoiceID, tblInvoice.Paid, tblCourse.ShortDescription
HAVING (((Last(tblDate.dtDate)) Like "*" & [Forms]![frmReminder]![txtDate] & "*"));

 

[ajetrumpet]

What happens is, if there are 4 last lessons on Sept. 17, it displays reminders for 4 students in a datasheet subform. However, Sept. 17 may only be the second last or third lesson for some of the students. It just randomly shows a student that has a lesson on Sept.17, not their last lesson.

Well, the purpose of the LAST function is to select the last entry in a record set that satisfies criteria (if there is any), so if 4 students each had a lesson on Sept. 17th, that might be the reason why you are getting that answer.

Plus, your criteria section will do this anyway. If today is Sept. 17th, and one of your students had a final lesson today, but 3 other students also had lessons (but not their final ones), your criteria section will produce exactly what you see, because that is what you have asked for with the statement.

I would think that something like...

HAVING (((Last(tblDate.dtDate)) Like "*" & [Forms]![frmReminder]![txtDate] & "*" AND 
Like "*" & [Forms]![frmReminder]![studentsname] & "*"));

would narrow the search to elimate the problem, but it's just a thought to consider...

 

[neileg]

Just to add to that, Last and First don't work how you expect them to all the time. Since a table is an unordered dataset, you really have no idea which record is first and which is last. Even when you sort the table, you are only sorting the display, not the data. Now if you use a query and apply a sort to the query, then Last and First will work as you expect, because the dataset itself has been sorted. Max and Min on the other hand do work as you would expect so I use these instead, if it is appropriate.

 

[zan]

oh wow! Max works perfectly!

I just love this forum...
I basically built the whole database with help and knowledge here.

Thanks a lot, ajetrumpet and neileg.

 

[ajetrumpet]

I agree Zan. I have learned quite a lot from the people here as well.

How about I take a minute to say thanks....
Thanks everyone!!