Calculating values in a field without a consistent expression

[moogoesthecow123]

I am currently trying to update my company's training log which is stored in access and am looking for some assistance. Two of the fields in the table are [Train Date] and [Expiration]. Unfortunately, most of the time [Expiration] has been left blank and I am seeking to add training expirations where necessary. To complicate this, some trainings expire after 1 year, some 3, some 5, and some do not expire at all so I am not able to make 1 change across the board with a calculated field or something of the sort. Is there any way that I would be able automate the process (with a query maybe?) so that I can, for example, add an expiration of 3 years after train date for all forklift trainings? I am not super fluent in access so if there is a simple fix that I am overlooking, that'd be awesome.

Thanks in advance!

[orange]

Do you have a list of Training Courses/Sessions?
Who determines how long the training credit is "good for"?
There must be some business rules or training courses and how they apply to your org somewhere.

[Micron]

Do you have a field in a table that specifies the duration that the training is valid, and is it consistent in terms of the units of time? That is, it doesn't say 3 years in one record and 6 months in another.

[moogoesthecow123]

Do you have a list of Training Courses/Sessions?
Who determines how long the training credit is "good for"?
There must be some business rules or training courses and how they apply to your org somewhere.

1) Fields in the table in question are [Man Number], [Course Name], [Instructor], [Train Date], and [Expiration]. I have an additional table which lists all of the training courses which is linked to [Course Name].

2) How long a training credit is good for is as determined by OSHA/the training organization

[mike60smart]

Hi

You said you have:- "I have an additional table which lists all of the training courses"

If this table is called tblTraining.

You just need to add a field named "DurationInMonths" - make its DataType - Numeric

Add in all of the Durations and then you can use this value to automate the ExpirationDate

[moogoesthecow123]

Once I added a "DurationInMonths" field to the training table, could you explain more in detail how I would automate ExpirationDate?

[mike60smart]

Hi

OK On your Form where you add details of Course Taken you would have the following:-

1. Combobox to select the Coursename together with the DurationInMonths - name this Combobox "cboCourse"

2. An Unbound textbox named "Duration" you would use the following to get the DurationInMonths Value - =[cboCourse].Column(2)

3. Control to Enter the TrainingDate

4. An Unbound Control where you would use the following to work out the ExpirationDate - =DateAdd("m",[TrainingDate],[Duration])

If you can upload your database I can give you an example.