IIF Statement problem

[bobburg]

Can anybody tell me what I an doing wrong in my IIf statement.

OTD 1: IIf([1st OTD Colour Points]=[2nd OTD Colour Points],2,IIf([1st OTD Colour Points]=[2nd OTD Colour Points] and [2nd OTD Colour Points]=[3rd OTD Colour Points],3,IIf([1st OTD Colour Points]=[2nd OTD Colour Points] And [2nd OTD Colour Points]=[3rd OTD Colour Points] And [3rd OTD Colour Points]=[4th OTD Colour Points],4,1)))

1st,2nd,3rd & 4th OTD Colour Points are =1

I am trying to get a result of 4 put can only get it up to 2.

Bob

 

[RichO]

Because 1st and 2nd are equal (both = 1) you are satisfying the very first condition of the IIf where it returns 2 and it goes no further.

There has to be an more efficient way to do this. Exactly what are you trying to accomplish (conditions & calculations, etc)?

 

[bobburg]

I am creating a report card for supplier delivery or On-Time Delivery. OTD. The colour Points represents a number which represent a colour for the supplier performance.
eg. Gold is 1 point, Silver is 2 points, Bronze is 3 points, Yellow is 4 points and red is 5 points. I have a query that shows the colour points for each month of the year for each monthly report card. One of the displays on the report card is # of months at this colour. That is number of months in a row with this colour. eg Jan was 5 points , Feb was 5 points, Mar was 5 points. The number of months at this colour would be 3. If feb was 4 points the number of months at this colour on the march report would be 1. I am trying to use a bunch of iif statements to calculate this number.

 

[RichO]

OK I understand. I see that you have fields named "1st OTD Colour Points" through 4th. Are there only 4 of these fields and are they table fields or fields you are creating in your query?

I'm trying to visualize the structure of your table. How is your table set up for keeping track of these points (ie: fields, etc)?

 

[bobburg]

They are calculated fields. There are 12 to deal with. one for each month of the year. I tried doing the iif statement backwards and it works except two problems. It looks at the 0's which I don't want and I max out on the IIF statements. I guess I could use mulitiple queries to have the max. problem but not sure what to do about the 0's. Any ideas?

 

[RichO]

What happens if the OTD points are 2 for Jan-Apr, but then 3 for June-Sep? You have 4 consecutive 2's and 4 consecutive 3's in the same year. How would you want it to compute that?

This is a tough one to find an easy solution for. If I was doing this I would want to bring these 12 parameters into VBA so I could loop through them to get the result. The way you have it set up, this would involve a function with 12 arguments and then passing the 12 values to an array so you can loop through them.