DLast or DMax or What?

[Guest]

Hi,

I have a form (Swim Lessons Records Form) with 3 fields that need to
calculate data taken from it's subform (Swim Lesson Detail Table subform).
The subform is viewed as a datasheet on the main form to show a history of
swim lessons the student has taken and has included on it these fields:

Level Date Completed Instructor PFI Next Level

I enter the Level they enrolled in, the Date the course was completed, the
Instructor (from a drop down list) and if they Passed, Failed or Incomplete
(again from a drop down). Next level figures from an expression:
=IIf([PFI]="Pass",[Level]+1,[Level]).... so if the student passes it figures
the next level as one course higher.

Now my problem. I made at the bottom of the main form fields that should
give me an overview of the student's progress on the subform. One field for
Next Level. I want this field to find the LAST Next Level entry in the
subform. I can make it find Next Level in the subform, but it picks the
first or top row in the datasheet. I need it to tell me the last or highest
level that student has attained so I can show it on a report as their new
Register Level. I also want to have a field in the main form that finds the
Last Date Taken or the last date the student took a class. Again, I can pick
up a date from the subform, but it is always chooses the first or top row in
the datasheet. I know I should be able to use DLast or something, but I
can't seem to get it to work correctly and keep getting an Error. I probably
don't have the syntax correct or I'm not pulling from the correct sourse or
something.

Hope someone can help.

Thanks!

Kass

 

[Duncan Bachen]

Hi,

I have a form (Swim Lessons Records Form) with 3 fields that need to
calculate data taken from it's subform (Swim Lesson Detail Table subform).
The subform is viewed as a datasheet on the main form to show a history of
swim lessons the student has taken and has included on it these fields:

Level Date Completed Instructor PFI Next Level

I enter the Level they enrolled in, the Date the course was completed, the
Instructor (from a drop down list) and if they Passed, Failed or Incomplete
(again from a drop down). Next level figures from an expression:
=IIf([PFI]="Pass",[Level]+1,[Level]).... so if the student passes it figures
the next level as one course higher.

Now my problem. I made at the bottom of the main form fields that should
give me an overview of the student's progress on the subform. One field for
Next Level. I want this field to find the LAST Next Level entry in the
subform. I can make it find Next Level in the subform, but it picks the
first or top row in the datasheet. I need it to tell me the last or highest
level that student has attained so I can show it on a report as their new
Register Level. I also want to have a field in the main form that finds the
Last Date Taken or the last date the student took a class. Again, I can pick
up a date from the subform, but it is always chooses the first or top row in
the datasheet. I know I should be able to use DLast or something, but I
can't seem to get it to work correctly and keep getting an Error. I probably
don't have the syntax correct or I'm not pulling from the correct sourse or
something.

Hope someone can help.

Thanks!

Kass

Dmax returns the highest value given certain criteria, and works against
a data source such as a table or query.
What you'd want to do is to use it on the same record source that your
subform is based on. You mention the autocalculate of the next level.
Since you aren't storing that in the table, you'll probably need to
recalc it.

Dim dtmLastDate as Date
Dim intLevel as Integer

dtmLastDate = DMax ("[DateCompleted]","[tblCourses]", "[PFI]='Pass'
AND [StudentID]=" & txtStudentIDFromMainForm)

That will return the highest date where the student from the main form
passed a test

intLevel = DLookup ("[Level]","[tblCourses]", "[DateCompleted] = #" &
dtmLastDate & "# AND [StudentID]=" & txtStudentIDFromMainForm) + 1

That will return the level from the same table, and add 1 to it.

Those are just two examples, using DMax and DLookup to get you some values.

Alternately, you could do some code that would use SQl to select the
Max([DateCompleted]) and then open a recordset and read the values returned.

 

[Guest]

Thanks Duncan, I'll try to digest this and give it a try. Thanks for your
time!

Hi,

I have a form (Swim Lessons Records Form) with 3 fields that need to
calculate data taken from it's subform (Swim Lesson Detail Table subform).
The subform is viewed as a datasheet on the main form to show a history of
swim lessons the student has taken and has included on it these fields:

Level Date Completed Instructor PFI Next Level

I enter the Level they enrolled in, the Date the course was completed, the
Instructor (from a drop down list) and if they Passed, Failed or Incomplete
(again from a drop down). Next level figures from an expression:
=IIf([PFI]="Pass",[Level]+1,[Level]).... so if the student passes it figures
the next level as one course higher.

Now my problem. I made at the bottom of the main form fields that should
give me an overview of the student's progress on the subform. One field for
Next Level. I want this field to find the LAST Next Level entry in the
subform. I can make it find Next Level in the subform, but it picks the
first or top row in the datasheet. I need it to tell me the last or highest
level that student has attained so I can show it on a report as their new
Register Level. I also want to have a field in the main form that finds the
Last Date Taken or the last date the student took a class. Again, I can pick
up a date from the subform, but it is always chooses the first or top row in
the datasheet. I know I should be able to use DLast or something, but I
can't seem to get it to work correctly and keep getting an Error. I probably
don't have the syntax correct or I'm not pulling from the correct sourse or
something.

Hope someone can help.

Thanks!

Kass

Dmax returns the highest value given certain criteria, and works against
a data source such as a table or query.
What you'd want to do is to use it on the same record source that your
subform is based on. You mention the autocalculate of the next level.
Since you aren't storing that in the table, you'll probably need to
recalc it.

Dim dtmLastDate as Date
Dim intLevel as Integer

dtmLastDate = DMax ("[DateCompleted]","[tblCourses]", "[PFI]='Pass'
AND [StudentID]=" & txtStudentIDFromMainForm)

That will return the highest date where the student from the main form
passed a test

intLevel = DLookup ("[Level]","[tblCourses]", "[DateCompleted] = #" &
dtmLastDate & "# AND [StudentID]=" & txtStudentIDFromMainForm) + 1

That will return the level from the same table, and add 1 to it.

Those are just two examples, using DMax and DLookup to get you some values.

Alternately, you could do some code that would use SQl to select the
Max([DateCompleted]) and then open a recordset and read the values returned.