Access 2003 - How do I build an expression to see the max value of a list from another form?

Question

I have a list of students. I have one table and associated table that contains their basic information. I have another table and form that contains assignment information, including when the assignment is given and date due. In the assignment form, I can filter it to see all the assignments that a particular student has not submitted and a field that shows how many days until due/past due each assignment is - using the DateDiff function. I want to be able to make a field on the BASIC INFO form that returns the max value of that particular student's overdue assignments (ie. returns a number of the days past due for the MOST overdue assignment). Currently I can't figure out how to do this, because the assignments form field that shows the overdue number of days is caculated via the DateDiff only when that form is opened.

Thanks!

Answer 1

Actually, it seems like you're making it a little harder on yourself than it needs to be. You don't want to get the info based on the list, you want to get the info based on the tables.

I'm assuming that the "days past due" is based on assignments that were never turned in (as opposed to assignments that were turned in late. It's a little more complicated for assignments that were turned in late, and I'll go into that in a minute).

For assignments that have never been turned in, you just need to get the earliest DateDue, for the current student, where the assignment was never turned in.

Here's an example of the function using the following information:
- The table is called "tblAssignments"
- The tblAssignments table keeps track of what student the record is for, through the "StudentID" foreign key, which is a Long Integer field. The current form also has a StudentID textbox on it somewhere.
- The field that stores the date due is called "DateDue"
- The field that stores the assignment turned-in date is called, "DateTurnedIn" If the assignment was never turned in, then the field is null (empty).

You create a textbox on the form and set the control source to the following:
=DMin("DateDue","tblAssignments","[StudentID]=" & [StudentID] & " AND DateTurnedIn Is Null")

If you keep track of whether an assignment was turned in through a Yes/No field instead of a date field (we'll call the Yes/No field "IsTurnedIn") then the formula would be changed to the following:
=DMin("DateDue","tblAssignments","[StudentID]=" & [StudentID] & " AND IsTurnedIn = False")

Either of these will return the due date of the earliest assignment that has not been turned in. If you like, you can then create another textbox that uses the DateDiff function based on the value in the first textbox.

As I said, it's a little more complicated if you want to include assignments that WERE turned in - just late. In that case, you'd need to create a query that calculates the differences, then have the textbox read something like the following:
=DMax("OverdueAmount","qryAssignmentsAndOverdueAmounts","StudentID = " & [StudentID])

Answer 2

hey visit http://msaccess.batcave.net