IIf and Dates

[WayneS]

I am trying to calculate the warranty expiration date on some records in a
table. WarrantyStartDate is Date/Time and WarrantyYears is Integer.

I have this expression in a query:

WarrantyExpire:
IIf([WarrantyYears]=1,DateAdd("y",1,[WarrantyStartDate]),IIf([WarrantyYears]=2,DateAdd("y",2,[WarrantyStartDate]),"Check"))

I receive this message:

'Data type mismatch in criteria expression.'

How can I restate this expression?

Thanks

 

[Ofer]

First to add years you should use the dateadd as follow

IIf([WarrantyYears]=1,DateAdd("yyyy",1,[WarrantyStartDate]),IIf([WarrantyYears]=2,DateAdd("yyyy",2,[WarrantyStartDate]),"Check"))

Does WarrantyStartDate field always has a value in it? the null might cause
an

 

[Douglas J. Steele]

What does the SQL of your query look like?

BTW, to add a year to a date, you need to use DateAdd("yyyy", ...), not
DateAdd("y", ...)

 

[WayneS]

Thanks, your suggestions were very helpful.

Here is the final expression:

IIf([WarrantyYears]>0,IIf([WarrantyYears]=1,DateAdd("yyyy",1,[WarrantyStartDate]),IIf([WarrantyYears]=2,DateAdd("yyyy",2,[WarrantyStartDate]),IIf([WarrantyYears]=3,DateAdd("yyyy",3,[WarrantyStartDate]),IIf([WarrantyYears]=4,DateAdd("yyyy",4,[WarrantyStartDate]),Null)))))

Thanks again,
Wayne