Datediff in query

[BasicBoy]

I have a table with a reminderdate field, a reminderdescription field, a recurinterval field (1 for the reminderdate, 2 for one year after reminderdate, 3 for one month after reminderdate and so on ...
and a lastintervalclosed.

I need to calculate when this reminder will be like in :

If the recurinterval is 2, a certain number of datediff years (contained in lastintervalclosed) must be added to the reminderdate to get the new future reminderdate (recurdate)

I tried this, but I cannot get it right.

Query = "Select reminderdate, reminderdescription, recurinterval, iif(recurinterval=1,reminderdate,iif(recurinterval=2,dateadd('yyyy',lastintervalclosed,reminderdate),' ')) as recurdate where date=#" & EventDate & "#"

In the ' ' space I need to also put the calculation for months and weeks and days.

Thank you

 

[PHV]

A starting point:
Iif(recurinterval=1,reminderdate,Iif(recurinterval=2,DateAdd('yyyy',lastintervalclosed,reminderdate),Iif(recurinterval=3,DateAdd('m',lastintervalclosed,reminderdate),' '))) AS recurdate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886