Date Calculation

[Guest]

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

 

[Guest]

Sorry for posting a query question in the wrong category.

 

[Guest]

I don't get the same error as you but I do get #Error on lines in my query
results where warrantyYears is not either 1 or 2. This is because
warrantyexpire is producing a date calculation but the exception to 1 or 2 is
"Check" which is a string.

 

[Guest]

First, what data type is WarrantyExipre? Also, is WarrantyStartDate a Date
field?
I don't think using the same variable for a string or a date is a good idea.

And, you are using a more complex statement than neccessary. Try this:


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

 

[Guest]

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.