How do you display zero when your query returns no rows?

[Novaember]

Hello again!
Welcome to my daily head scratcher.

I have a query that selects rows based on criteria then does some counting. The selecting and counting is fine as long as the criteria found some rows.

but if there are no rows selected instead of zero I get a blank cell.

here is an example:
I search my database for "Apple" in field [Fruit]
Then I do sum(IIF([Apple]="Red",1,0))
That counts Red Apples

But if there is no "Apple" instead of zero I get nothing.

How can I make it show a zero if there are either no rows or no Red Apples?

Thank you in advance!

 

[georgedwilkinson]

select count(*) from mytable where apple = 'Red'

 

[Novaember]

hmm
ok my query is actually a little more complicated than I let on. I'm not sure how to use the count (*) with it.

This is for a quality review database
I have fields: LetterCorrect, NameCorrect, AddressCorrect etc (there are a lot of fields)
with drop downs of Yes, No, NAA, NA

I am selecting the rows where ReviewType = Department OR Individual
Then I have sum(IIF[LetterCorrect]=No,1,0)+ IIF([NameCorrect]=NO,1,0) + IIF([AddressCorrect]=NO,1,0)
This counts how many items in each row are = to NO. There can be more than one item in each row that is a NO.

Would I put in my review type criteria then say:
count (*)IIF([LetterCorrect]=No,1,0) + IIF([NameCorrect]=No,1,0) etc?

I don't want to count the rows. I want to count the items = NO within the rows and if there are no items = No or no rows I want to display zero.

 

[MSAccessRookie]

Hello again!
Welcome to my daily head scratcher.

I have a query that selects rows based on criteria then does some counting. The selecting and counting is fine as long as the criteria found some rows.

but if there are no rows selected instead of zero I get a blank cell.

here is an example:
I search my database for "Apple" in field [Fruit]
Then I do sum(IIF([Apple]="Red",1,0))
That counts Red Apples

But if there is no "Apple" instead of zero I get nothing.

How can I make it show a zero if there are either no rows or no Red Apples?

Thank you in advance!

Try using the Nz() Function instead of the 0 for the False option

 

[georgedwilkinson]

Hmmmm. Multiple fields with duplicate functionality and data. Where have I heard of this before?

You may need to normalize before trying this or you're bound to spaghetti code for the life of the system.

 

[ajetrumpet]

how about something like this:

IIF(

DCOUNT(

"apple", "fruit", "[apple] = 'red'") = 0, 

0, 

sum(

IIF([Apple]="Red",1,0)))