=Count(IIf([ works for one field, but not another

[Correenie]

Please help if you can.

=Count(IIf([His Age]<"31" And [His Age]>"1",1))/Count([His Age])

works fine for giving me the percentage of people who are 30 and under, but

=Count(IIf([Combined Income]<"70000" And [Combined Income]>"1",1))/Count([Combined Income])

will not work (in the same report off the same query).

Please advise.

 

[boblarson]

If Combined Income is a number, get rid of the quotation marks around 70000 and the 1.

=Count(IIf([Combined Income]<70000 And [Combined Income]>1,1,0))/Count([Combined Income])

 

[Correenie]

thanks - tried and failed.

I have the same field (with the same data) as both Text and Number (I've tried *everything*). I used your code with the number version, but it didn't work. It thinks that all numbers meet the criteria.

Combined Income
80000
62000
100000
90000
120000
150000
80000
200000
60000
60000
68000
70000
130000
120000

Ranges I need: Under $70K, $70-$79999K, and $80+K

 

[Moniker]

There it is. The right parentheses is incorrect. Change from:

=Count(IIf([Combined Income]<70000 And [Combined Income]>1,1,0))/Count([Combined Income])

To:

=Count(IIf([Combined Income]<70000 And [Combined Income]>1),1,0)/Count([Combined Income])

 

[Correenie]

"The expression you have entered has a function containing the wrong number of arguments." (Please help).

 

[Correenie]

"The expression you have entered has a function containing the wrong number of arguments." (Please help).

 

[Moniker]

My bad... try:

=Count(IIf(([Combined Income]<70000 And [Combined Income]>1),1,0))/Count([Combined Income])

You have to have the "This AND That" in its own parentheses when they are numerical.

 

[pbaldy]

You have to have the "This AND That" in its own parentheses when they are numerical.

Not on my PC.

 

[Brianwarnock]

I don't know how your first expression works because the correct expression is
=Sum(IIf([Combined Income]<70000 And [Combined Income]>1,1,0))/Count([Combined Income])

Note the Sum, you have to sum the number of records the meet the criteria.

Brian

PS I believe the 1st expression should be
=Sum(IIf([His Age]<31 And [His Age]>1,1))/Count([His Age])

 

[pbaldy]

you have to sum the number of records the meet the criteria.

I wondered about that, so I tested yesterday. It works fine as originally posted.

 

[Moniker]

I'm almost positive that you need the parentheses in a compound statement like that. It may sometimes work without them, but the results are not always what is expected. Since IIF is compound, the parentheses would need to be there. For example, this would screw things up:

If This AND That OR That AND SomethingElse

Whereas this would provide expected results:

If (This AND That) OR (That AND SomethingElse)

Just like math (which is all a comparison is at it's basest form), the stuff in the parentheses gets evaluated before the rest of the expression. I could be wrong here as far as that example IIF statement goes (the one that started this thread), and it may be my anal retentive nature shining through.

 

[pbaldy]

No question that when you have a mix of AND & OR, you'd better use parentheses. In math, if combining multiplication/division with addition/subtraction, you'd better have parentheses. However, in my experience, this situation does not require them (and I tested this specific formula).

 

[Brianwarnock]

I wondered about that, so I tested yesterday. It works fine as originally posted.

Just tested
Count gives a total count of all the records, which is what it is supposed to do.
Sum gives a total count of all the records meeting the criteria, ie returning a 1, it Sums the result of the IIf.

Brian

 

[pbaldy]

I respect your talents Brian, so I'm not sure if we're testing/expecting the same thing. I put this in the footer of a test report:

=Count(IIf([id]<20 And [id]>0,1))/Count([id])

and it gives me the correct percentage of those less than 20 to the total, which is what I interpret the OP's goal to be.

 

[Correenie]

Thanks, got the answer

Thank you to everyone who gave me advice. This is what finally worked:

=Sum(Abs([Combined Income]<70000 And [Combined Income]>1))/Count([Combined Income])

I was told that this is the correct way because: "IIf() should always have 3 arguments and you are using only 2. I would expect [Combined Income] is numeric."

 

[Brianwarnock]

Ummm, sorry for the delay, been having my eveing meal

I'm testing in a query, so the test environments are different, it would seem that in the report the IIf acts like the criteria field of a query, my knowledge is not good enough to give a definitive answer on this, but it looks as though we may be both correct, always a nice outcome, if only we knew why.

brian