Rita,
First off, I would not normally (almost never) store calculated values in
your table. It is far better to generate them in a query whenever I need
them. This is generally much faster, and less likely to result in bad data
(what happens if someone changes a date in one of your tables rather than in
code).
I assume that you have a table for your "types of training", I'll call it
tbl_Training_Types. I would add two fields (DurType, DurVal) to this to
indicate the length of time the training is valid. Where you enter into the
DurType a string value representing the Interval parameter use in the Dateadd
function. Values for this field would be ("yyyy", "q", "m") to represent
years, quarters, and months. Then, in the DurVal field, you would enter the
number of intervals (1, 3, ...).
Now you can create a query that generates info like below. The key to this
is that for each individual, you are likely to have multiple types of
training. If you create a new field, and new control on your forms everytime
someone institutes a new type of training, you will go crazy. Instead, you
need to use a subform that lists the types of training the individual has
taken and the expiration date of that training. This way, when someone adds
a new training requirement, all you have to do is add it to your
tbl_TrainingTypes table.
SELECT UserName,
TrainingType,
DateAdd(DurType, DurVal, [TrainingDate]) as TngExpires
FROM yourTable
INNER JOIN tbl_Training_Types
ON yourTable.TrainingType = tbl_TrainingTypes.TrainingType
Don't know enough about your table structure to determine whether you are
keeping track of all training dates in some sort of TrainingHistory table.
If you are, the above query would need a WHERE clause to limit it to the most
recent TrainingDate for each UserName/TrainingType combination. That might
look like:
WHERE [TrainingDate] = (SELECT MAX(TrainingDate) FROM yourTable T
WHERE T.UserName = yourTable.UserName
AND T.TrainingType =
yourTable.TrainingType)
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
Rita said:
I am tracking seminars staff attend. I need to track when certain training
expires (First Aide Training needs to be done every 3 years). Some training
is good for 1 year and others are good for 3 years.
These are my fields:
Type
Dropdown field with CPR, First Aide)
Date: Date of Seminar
Today-Date: Date()-[Date]
1Yr: IIf([Today-Date]>365,"Yes","No")
3Yr: IIf([Today-Date]>1095,"Yes","No")
FirstAide Expired: IIf([Type]="First Aide",[3Yr])
CPR Expired: IIf([Type]="CPR",[1Yr])
I get correct information returned on my 1YR and 3Yr fields (appropriate
“Yes†or “Noâ€).
I’m trying to create a FirstAide Expired field, and I’m just getting “#Errorâ€.
