Expiration date computation

[Design by Sue]

My database keeps track of employee training. A few procedures have a limit on how long the training is good for, ie 24 or 36 months. There is a field include in the database for the date the employee completed the training ie 2/14/2010. I need to create a report showing who need to retake the training because it has expired for that person. I have a query that results with all of the employees that have taken the procedures with an expiration. Can someone please help me complete the query so that the results list only those that have to retake the training. For example employee ID number 123 took procedure XYZ on 2/14/2010 and procedure XYZ expires in 24 months.

Thanks in advance

Sue

Forgot to state - using Access 2003

 

[Design by Sue]

I think I actually found the answer myself - I used the following expression to create a new field in the query DateAdd("m",[Expiration],[Date])

Maybe this will help another. The web page that explained it is here http://www.techonthenet.com/access/functions/date/dateadd.php.

and then used the criteria of <Date() for that field

 

[Mr. B]

You did not mention if you have a field in your table to hold the number of months until training is to be repeated.

The following assumes that you have the following in your table:
"tblEmpTraining" = name of your table
"LastTrainingDate" = last date when employee received training
"TrainingPeriod" = length in months training is valid (24, 36 or leave blank when training does not expire)
(You will need to substitute the names of your table and fields for the ones I have proposed above.)

Copy and paste the following into the "Field" row of the QBE:
NextTrainingDue: IIf([tblVehicleService]![TrainingPeriod]>0,DateAdd("m",[tblVehicleService]![TrainingPeriod],[tblVehicleService]![LastServiceDate]),"")

Use the following in the "Criteria" row of the QBE:
>"" And <=Date()

This will create the calculated column showing the actual date when the next training is due and the criteria will filter for only those records where the expiration date is less than or equal to the current date.