MBP wrote:
> 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.
--
-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.
|
Similar Threads
