Help with IIF expression please.....

[chef_tim]

Good morning. I'm trying to build an expression that will give a date to retest on fitness using four criteria Excellent, Good, Marginal, and Poor. I've got a start but it is getting very deep for me. If I could just get a a little help I think I'm close. Here is what I have so far....

=IIf([fitnessscore]="Excellent",”m”,+12,[fitnessassesment],IIf([fitnessscore]="good",”m”,+12,[fitnessassesment],IIf([fitnessscore]="marginal",”m”,+6,[fitnessassesment],iif([fitnessscore]=”poor”,”m”,+3[fitnessassesment]))))

Fitness assesment is the date thay took thier fitness test. Thanks for your help, Tim

 

[Brianwarnock]

I think what you are trying to do is

=IIf([fitnessscore]="Excellent",Dateadd(”m”,12,[fitnessassesment]),IIf([fitnessscore]="good",Dateadd(”m”,12,[fitnessassesment]),IIf([fitnessscore]="marginal",dateadd(”m”,6,[fitnessassesment]),iif([fitnessscore]=”poor”,dateadd(”m”,+,[fitnessassesment])))))

Brian

 

[chef_tim]

It is amazing how funny a few spelling errors will make this thing act That was just what I needed, the dateadd works great. Just as a final note, poor is within 90 days but not before 45 days. Could I do that with >< type of expression??? Thanks very much, Tim

 

[Brianwarnock]

I don't see how that fits in with your current design whereby you actually create a date for the retest, perhaps you should carry the date of the last test and check against that for the retest then yes you could check >45 and < 90.

Or you will need to carry 2 dates

I could perhaps suggest how I would do it if I knew more about your requirements,eg is the result of the query a report or to store the data?

Brian

 

[chef_tim]

I was thinking that I was working with two different types of data. It really isn't importaint to the overal data, I just need to remeber not to re-test them prior to 45 days of recieving a poor score. I took what you reworked and instead of having it in my report I put it into the queiry with the rest of the data then put it into my report. Works great, thanks again, Tim