My head is about to explode !

[trackmedic]

Trying to figure this one out!!!!!


I have a pull down menu on a form that runs a querie to choose a type of training and prints a report sorted by training showing employees and the date they took the training.

On the report, I want to add a text box that shows if the selected training is valid or expired. If all the training expired on yearly intervals, then all would be great. Well the five types of training expire on different intervals..1 year, 3 years one does not expire.

How would I syntax the expression to look at the training picked, and decide if it is expired or not using the info generated on the report. the report shows the training on the top picked from the query, the persons name and the date taken.


Thanks to all in advance.......!

 

[Travis]

How do you have the Tables Stuctured?


If you have a "Training" table that is a list of Different Training the solution is a simple calc.

tblTraining
TrainingID -> Unique ID
TrainingName -> External Name of the Training
CertLength -> Length of time the certification is good for in years (This could be done as months or days or even weeks)


You would then link this table with the table used to store what training was done and when. Use the DateDiff function in the query to see if the training has exceded the cert time.

 

[trackmedic]

So you are saying

That I would have to add another item to my training table for "cert info". Such as PSST training is good for 3 years. after that you would need to take it again.

Now,

I use a query to generate the report showing the type of training, persons name and date of training. I would have to add the "cert info" item to that query. how do I use the datediff function. Is it in the query or on the report?


Thanks

Andre'

 

[WayneRyan]

Andre,

You can use the DateDiff function in the query, or the report.
Probably easier in the query:

TimeDiffYears: DateDiff("y", [TrainingDate], Date())

Wayne

 

[WayneRyan]

Andre,

Upon further review ...

TimeDiffYears: DateDiff("y", DateAdd("ww", [TrainingDate], [NumWeeks]), Date())

Wayne

 

[trackmedic]

Thanks Wayne,

Thanks,


Still having no luck. I'll try the new one.


Andre'

 

[WayneRyan]

Andre,

It seems that all of the ingredients to do this are in this
thread. If you add a field for how many weeks the training
is good for, you can calculate if training is current or not
when you run the report.

If you added this to a query, then your report could just
reference ThisCheckBox for its value:

ThisCheckBox: Iif(DateAdd("ww", [TrainingDate], [NumWeeks]) <= Date(), False, True)

Wayne

 

[trackmedic]

hmmmm....

I think I see what you mean. My course dates are per years. So, I would substitute the "ww" with "yyyy". And where would I put the text "out of Compliace" in the formula.


Sorry if I sound stupid, I'm sort of a beginner.



Thanks for everything

Andre'

 

[Travis]

ThisCheckBox: Iif(DateAdd("yyyy", [TrainingDate], [NumYears]) <= Date(), "Out of Compliace", "In Compliace")

 

[trackmedic]

GOT IT !

Thanks for all the help !


Andre'