A problem with DateAdd

[eddie442]

I'm working on a database where the user has to enter a date in a field called DateofCalibration. The next field (CalibrationInterval) needs to be a drop down list detailing timescales ranging from 6 months to ten years. On entering a timescale, the DateofNextCalibration field will automatically update to reflect the new date, using the code =DateAdd("yyyy",[CalibrationInterval],[DateofCalibration]). This works fine when I update for years, but I need to be able to update for months as well. How do I do this? Would I need to write an IF....ELSE statement in VBA? If so, under what event would I put the code?
I would appreciate any help!

Cheers
Dave
England

 

[FoFa]

Just change your dateadd to use months instead of years, and calculate the months if years is given.
DATEADD("m",[CalibrationInterval],[DateofCalibration])

If they want a 3 year interval
if intervaltype = years then
CalibrationInterval = Numyears * 12
else
CalibrationInterval = NumMonths
End IF

As an example, how you implement it depends on your application.
Our calibration system uses days for everything, so we always calculate down to days then calculate the next calibration date from that.