Autocalculate expir date for qualif based on course exp dates

[Guest]

Problem: What I need it to do is autocalculate the QualExpDt. Scenario is
that to qualify for certain QUALIFICATIONS, certain courses have to be
completed - could be 1 couse, could be 10. Each course will have an
expiration date. If any of the courses expire related to the qualification,
the qualification expires.

Example:

Employee - Donald Duck
Badge #: 44989

Donald has had 3 courses that qualify him to be on the Hazmat Strike Team
(QL18). He took the courses below on the following dates.
His QualExpDt for QL18 will be 2/1/06 since this is the oldest course
expiration date. He becomes unqualified on 2/1/06 until he updates the
course C1 HazMat 1.

Courses required to be taken to qualify for QL18:

C1 HazMat 1 Expires 2/1/06
C2 HazMat 2 Expires 3/13/06
C3 General Hazard Communication Expires 2/15/06

Can you help me program the Qualfiication expiration date field to auto
calculate based on: employee, then oldest course expiration date?

Thanks!

 

[Duncan Bachen]

Problem: What I need it to do is autocalculate the QualExpDt. Scenario is
that to qualify for certain QUALIFICATIONS, certain courses have to be
completed - could be 1 couse, could be 10. Each course will have an
expiration date. If any of the courses expire related to the qualification,
the qualification expires.

Example:

Employee - Donald Duck
Badge #: 44989

Donald has had 3 courses that qualify him to be on the Hazmat Strike Team
(QL18). He took the courses below on the following dates.
His QualExpDt for QL18 will be 2/1/06 since this is the oldest course
expiration date. He becomes unqualified on 2/1/06 until he updates the
course C1 HazMat 1.

Courses required to be taken to qualify for QL18:

C1 HazMat 1 Expires 2/1/06
C2 HazMat 2 Expires 3/13/06
C3 General Hazard Communication Expires 2/15/06

Can you help me program the Qualfiication expiration date field to auto
calculate based on: employee, then oldest course expiration date?

Thanks!

Assuming that their qualifications are stored on a table which includes
their Employee ID and the QualID, you could use the Min() function (in
an SQL statement) or the DMin() function on the Date Field to find the
lowest date.

One example:

Dim dtmOldestQual as Date

dtmOldestQual = DMin("[DateExp]","[tblQualifications]","[EmpID] = 123")

If dtmOldestQual < Date Then
msgbox "You're not qualified"
Else
msgbox "You're qualified"
End If

Depends where you want this kinda thing to work when you refer to
autocalculate. You have several different ways to accomplish this, in a
query, a report, as a field on a form, etc.