IIF with a "and" statement

[debbiecarvery]

Hello,

Jim and bob helped me out with this statement but now I would like to add something, and again it is not working, perhaps someone can help .

The Total VC area works, I want these numbers 12, 14 or 16 to print only if Equipe equals "no", I have also tried putting the [equipe] = "no" at the beginning of my statement.


=(IIf([Sum of Total VC] Between 550 And 599,12,IIf([Sum of total VC] Between 600 And 649,14,IIf([sum of total vc]>=650,16,0)))) And [Equipe]="no"

Thanks,
Debbie

 

[GoDawgs]

Try this:

=IIf([Equipe]="no",(IIf([Sum of Total VC] Between 550 And 599,12,IIf([Sum of total VC] Between 600 And 649,14,IIf([sum of total vc]>=650,16,0)))))

that should do the trick, hope that helps.

Kevin

 

[debbiecarvery]

Thanks Kevin,

I tried this and now nothing is printing. I checked to make sure my data actually had some fields where the equipe = no and there are definitely a few. The rest of the formula works it is when I try to add the [equipe]= no, I have no clue what I am doing wrong, any other suggestions

Thanks
Debbie

 

[GoDawgs]

First off is the field actually a text field? Does [equipe] actually = "no"...just like that? Just making sure.

 

[debbiecarvery]

Yes, it is a check box, if I play in my queries and use "yes" or "no" I get the correct results. I "could" do this in a query, the only thing is I want to use one report to calculate totals on "equipe" with a no value and different totals on "equipe" with a yes value.

Thanks
Debbie

 

[GoDawgs]

This is a shot in the dark, but try substituting this:

=IIf([Equipe]="no",(

with this:

=IIf([Equipe]=-1,(

or

=IIF([Equipe]=0,(

I can't remember what the value of an unchecked check box is...it's one of those two...

 

[debbiecarvery]

Thank You !!!!!, -1 is giving me my "yes" that I need and 0 my "no".

Debbie

 

[GoDawgs]

Yea, thank goodness, I was getting worried there. Glad I could help.

Kevin

 

[JoeMiller]

Another way to do this when you have more than two possible answers is to use the Switch() function. It's much easier to write than nested IIf statements. Here's how what you have would be written using Switch()

Switch([Sum of Total VC] Between 550 And 599,"12",[Sum of Total VC] Between 600 And 649,"14",[Sum of Total VC]>650,"16&quot

Since I don't understand what you want to show when these values aren't met, I'm going to assume you want to show nothing. So you would combine the above with an IIf statement to first test your [Equipe] field for no and if it's yes then evaluate the switch function. Here's what it looks like that way:

IIf([Equipe]=Yes,Switch([Sum of Total VC] Between 550 And 599,"12",[Sum of Total VC] Between 600 And 649,"14",[Sum of Total VC]>650,"16&quot,"&quot

I know you have this working already, just wanted to present another option that IMHO is a little cleaner than the nested iif avenue.

HTH Joe Miller
joe.miller@flotech.net

 

[scriverb]

JoeMiller: I am the Bob in the orignal post and I concurr. In Debbie's last questions I provided a similar demonstration of the Switch just to show her how it works. It is much easier to use and manage the logic.

Debbie, abandon those nexted IF's as they will drive you crazy if you have more than 2 in a statement.

Bob Scriver