Validate Date Entered is Between 2 serial dates

[MER2007]

Hi!

I have a database that is tracking certification. I need to look at the first training date example of one person who as participated in trainings--we have no proof of training in the first 3 years, but we do for subsequent years--(CET=Continuing Education Trainings):

Between 4/24/01 and 4/24/02 he needed a CET = ??
Between 4/24/02 and 4/24/03 he needed a CET = ??
Between 4/24/03 and 4/24/04 he needed a CET = ??
Between 4/24/04 and 4/24/05 he needed a CET = 5/19/04
Between 4/24/05 and 4/24/06 he needed a CET = 4/29/05
Between 4/24/06 and 4/24/07 he needed a CET = 10/13/06
Between 4/24/07 and 4/24/08 he needs a CET.........whi ch means he should be currently certified and next due date should be 4/28/2008, but since I use:

If Year(vLastCET) >= Year(vNextCET) Then
Me!vNextCET = DateAdd("YYYY", 1, vNextCET)

And the date you see there in the example 10/13/06 only advances the next due date to 4/28/2006 because the year the person took the training is in 2006. Any help you can provide on how to better program this would be wonderful. The due date is based annually on the original training date of 04/24/2001.

[Killer42]

I'm not sure I understood the question. But I've just written this function which may be helpful. It accepts a date, and returns the next "anniversar y" of that date. So if you called it now (for future reference, today is 13th July 2007) and passed it 4/28/2006 it would return 4/28/2008.

[CODE=vb]Public Function NextAnniversary Of(ByVal SomeDate As Date) As Date
' Return the next anniversary of the specified date.
NextAnniversary Of = DateSerial(Year (Now), Month(SomeDate) , Day(SomeDate))
If NextAnniversary Of < Now Then
NextAnniversary Of = DateAdd("yyyy", 1, NextAnniversary Of)
End If
End Function[/CODE]This function is written in VB6 but should work (I hope) in most versions of VB, including VBA in MS Access. If it is placed in a code module in Access as a public function, you should be able to include it in SQL queries against that database.