I've successfully created nested IIf statements in the past to update fields with new values, based on pre-existing values. The problem I'm having now is setting a new value in a blank field based on date values from an exisiting field using < and >.
Here's my non-working UPDATE statement:
UPDATE OLS SET OLS.MULTIPLE = IIf(OLS.FPDate<="200401,'60',IIf(OLS.FPDate<'200501' AND OLS.FPDate>'200401','48','36'))
WHERE OLS.FPDate IS NOT NULL
What I'm trying to do is set purchase multiples based on dates. Basically anything older than 3 years at 60 months, 2-3 years at 48 months, 1 year or less at 36 months.
If I should think about constructing this differently please let me know. Thanks!