Combine two IIF

[USMCFROSTY]

Im trying to take the total of the two below and subtract it from 100 for my total who left the field blank.
=Count(IIf([status]="Acceptable",0)) and =Count(IIf([status]="Not Acceptable",0))

Example: 50 "acceptable"
40 "not accptable"
How can i take the total of these two and subtract from 100 for a third box?
Third box should read 10 "those who left it blank.

I did try =Count(IIf([status]="",0)) but this treats it like somthing is in the cell when it is blank.

I also tried =Sum(IIf(IsNull([Status])=True,1,0)) and this gives me the blanks but not all the blanks. It doesn't account for the blanks that came up in =Count(IIf([status]="",0))

 

[AUGuy]

Assuming you're talking about a query, why not use this:

[COLOR=blue]Count: Sum(IIf([status]="Acceptable",1,0))[/COLOR]

 

[USMCFROSTY]

The qry populated the report and the iif are in the report. 3 txt boxes
I needed to show the 3 status
Acceptable
Not acceptable
blank

 

[vbaInet]

This:

=Count(IIF(Len([status] & "") = 0, 1, Null)) - Count(IIF(Right([Status] & "", 10) = 'Acceptable'), 1, Null)

 

[AUGuy]

The qry populated the report and the iif are in the report. 3 txt boxes
I needed to show the 3 status
Acceptable
Not acceptable
blank

Sorry, I misunderstood. vbaInet's solution works.

 

[USMCFROSTY]

I get an error
"The expression you entered has a function containing the wrong number of arguments"
Also im not sure i understand where not acceptable comes in?

 

[vbaInet]

I wrote that in Notepad. This should be more syntactically correct:

=Count(IIF(Len([status] & "") = 0, 1, Null)) - Count(IIF(Right([Status] & "", 10) = 'Acceptable', 1, Null))

If you have just Not Acceptable and Acceptable then that will work. If, however, you have other words ending in the word "acceptable", then it won't work. Let us know.

 

[USMCFROSTY]

My results are 33 acceptable, 23 not acceptable and with your iif i get 33. I have a total of 145 records that will not change, i said 100 before to keep it simple. Does this make a difference in the formula? 33+23=56 records with acceptable or not acceptable. So 89 should be blank 145-56=89
With your if im getting 33
Im lost

 

[USMCFROSTY]

Re: Combine two IIF Summary

I have a form with a combo box "acceptable", "not acceptable". I also have a report to show the status of each. I show the totals of each through two text boxes.
=Count(IIf([status]="Acceptable",0)) and =Count(IIf([status]="Not Acceptable",0)). I need a third box that tells me how many are blank. (Total of 145 records) I tried different formulas for blank but am getting false info. Some blank records are showing up as having data when they are blank. So... i wanted to just take the two that i have now and combine them and just subtract from my 145 and this would give me the blanks. Problem is that i cant combine the two and - the 145 in a single formula.
Hope this helps.

 

[vbaInet]

You said in your original post that you want to subtract Acceptable + Not Acceptable from Blanks. That is,

Blanks - (Acceptable + Not Acceptable)

From your response in post #8, you said you should have 89 blanks, 33 Acceptable and 23 Not Acceptable. The code provided does this:

89 - (33 + 23) = 89 - 56 = 33

89 blanks = Count(IIF(Len([status] & "") = 0, 1, Null))
33 Acceptable + 23 Not Acceptable = Count(IIF(Right([Status] & "", 10) = 'Acceptable', 1, Null)

Is that clearer now?

 

[USMCFROSTY]

to keep it simple i need acceptable + not acceptable + blanks to total 145........

my iif for blanks did not work so i want to have one statement that says this.
Acceptable + not acceptable = X, X minus 145 = B B will tell me the number of blanks.

Example:
30 acceptable + 15 not acceptable = 45 answers out of 145(total)
I now want to take the 45 answers and subtract it from 145. This will be the 100 blanks.

Thanks for your patience with me.

 

[vbaInet]

If you want the number of blanks use just:

= Count(IIF(Len([status] & "") = 0, 1, Null))

What do you get?

By the way, most of the time we can see when a post has been posted twice. If you're being helped with a problem everyone of us helping can see that thread and will chime in if needs be.

 

[USMCFROSTY]

that works for blanks. 89 great!

Why does this formula =Count(IIf([status]="",0)) show 67
and this =Sum(IIf(IsNull([Status])=True,1,0) show 22 so both of these = 89
Both the 67 and 22 are blank cells, and if i put in excell and count blanks its 89 but in access only your last formula works. Could you clarift or point me to where i should read up?

Thank you for helping out!

 

[vbaInet]

There's a difference between Blank (i.e. Null) and zero-length string (i.e. "").

Null means no value, whilst "" means it is a string of zero length.

So if you test using just IsNull([Field]) you will get just the Null count. If you test using Len([Field] & "") you will get the Null + Zero-length String count. IsNull() in Access is similar to IsBlank() in Excel.

What I advice you do is go back to the table and under the properties for that [Status] field, set the Allow Zero Length property to No. That way you can just test using IsNull().

 

[USMCFROSTY]

Now your formula is clear. Thanks again!

 

[vbaInet]

You're welcome!

 

[USMCFROSTY]

Achieved = 25
In progress = 0
Formula returns 1 ="26"-Count(IIf(Right([Status] & "",10)='Achieved',1,Null))
Total = 26
This works fine for 25 "achieved" and zero "in progress"

Problem is when "In progress" is greater than "Achieved"
Achieved = 8
In progress = 17
Formula returns 18 ?
Any thoughts

Im trying to combine these two formulas and subtract from a set number.
=Count(IIf([Status for 1]="Achieved",0))
+
=Count(IIf([Status for 1]="In Progress",0))
-
26
What we talked about before works but im on a new form and due to table design on my part i need to fix the report now and work on the tbl.

 

[vbaInet]

The two scenarios you've written are not accurate.

25 - 0 is not equal to 26. I guess this was just a typo.

8 - 17 cannot be equal to 18. How can you prove this is the case? Access can never subtract wrongly.

What you wrote -->

Count(IIf([Status for 1]="Achieved",0))

is not the same format as what I gave you -->

Count(IIf([Status for 1]="Achieved",0, Null))

 

[USMCFROSTY]

I have 3 text boxes
=Count(IIf([Status]="Achieved",0)) gives me 25
=Count(IIf([Status]="In Progress",0)) gives me 0
I have a third text box with
="26"-Count(IIf(Right([Status] & "",10)='Achieved',1,Null)) which gives me 1
The above works, below is the problem

When "In progress" is greater than "Achieved" it doesnt work.

 

[vbaInet]

Remember that this -->

Count(IIf(Right([Status] & "",10)='Achieved',1,Null))

is giving you a count of Not Achieved and Achieved.

You said you want to combine the formulas, you don't need to. If a formula is already in a textbox, use the value of the textbox for your calculation. So:

=[AchievedTextboxName] + [InProgressTextboxName] - 26

Also, don't put 26 in quotes. It's a number not a string.