Calculating a Date Fields based on 2 Different Dates (1 Viewer)

[Smokeeater]

I have three date fields. [DateA], [DateB], [DateC].

[DateC] is a calculated field in my query, that adds 2 years to [DateA], and it works fine via this criteria:

DateC_2: DateAdd("yyyy",2,[DateA])

However, [DateA] is only used one time, and then all follow up activities are defined in [DateB].

What I would like to do is maintain the current the 2 year addition for [DateA] in [DateC], until such time [DateB] is used, and then it would reset the date in [DateC] based on the date in [DateB] + 2 years.

Sort of like:
Criteria 1: [DateC] = [DateA] + 2 Years from date entered, until
Criteria 2: [DateC] = [DateB] + 2 Years from date entered

I tried this, but it returned date value of -1.
DateAdd("yyyy",2,[DateA]) or DateAdd("yyyy",2,[DateB])

Thanks in advance for any assistance

 

[CJ_London]

perhaps explain what you are trying to do rather than how you are doing it? Is this for some sort of maintenance schedule?

I don't understand this

until such time [DateB] is used

please clarify

 

[arnelgp]

DateC_2: IIf(IsNull([DateB]),IIf(IsNull([DateA]),Null,DateAdd("yyyy",2,[DateA])),DateAdd("yyyy",2,[DateB]))

 

[Smokeeater]

These are used to track the issuance of licenses related to operating powered equipment, such as a forktruck. DateA is the initial date that the person received their license. We want to maintain this date from a history standpoint, so that we know how long they have been licensed. When the license is issued, it is valid for only 2 years = DateC. At the 2-yr make, they must be re-evaluated on the equipment, and then a "new" 2-yr. license is issued (DateB). Then that occurs, we need to reset the date in DateC so that we can track the next re-eval date, and so forth.

 

[CJ_London]

sounds like you need a linking table with dates

tblEmployees
EmployeePK
EmployeeName
...
...

tblCertifications
CertificationPK
CertificationName
CertificationFrequency
...
...

tblEmpCertifications
EmpCertificationPK
EmployeeFK
CertificationFK
CertificationDate

There are a number of ways you can modify the above, but the idea is when you create a new certification record you calculate the next one by adding the frequency to the latest certification date.

 

[Smokeeater]

Thanks CJ. I will take a deeper look at the current structure, but we will probably stick with the one table method.

 

[Smokeeater]

DateC_2: IIf(IsNull([DateB]),IIf(IsNull([DateA]),Null,DateAdd("yyyy",2,[DateA])),DateAdd("yyyy",2,[DateB]))

arnelgp - thanks so much. I am out of the project for today due to meetings but will give this a try first thing tomorrow morning. I will let you know how it works out.

 

[Smokeeater]

I was over complicating this by trying to do the calculations in the underlying query. What I tried, and it worked, was putting the code in the After Update function for fields A and B.

DateA adds 2 years to both DateB and DateC after selecting the date
DateB adds 2 years to DateC after selecting a new date.

I am going to carry it one step further and lock DateA since the users want to maintain the initial date the license was issued. I will make it so they can unlock it if need be, but it will need to be a deliberate action, and not one that involves simply changing the date.

Thanks for all of the replies. It really helped me get to where I am now.