use null cells in query

Question

I have a query with 12 queries in it.

Each has a count expression in it names total#.

My expression is exp1: count[total1]+[total2}+ etc to 12

all totals are blank except for total4

when I run the query it is blank

I need it ti show "1" for 1 entry

I know the problem is with the empty expressions but I don't know what to do to fix it

I tried is not null and not isnull in criteria but it doesn't work.

I can't fill the empty "exp" with "0" because then it counts them as an entry giving a count of 12 with 1 entry

thanks for any help

Answer 1

Actually I am trying to do both I figure if I can get 1 to work I can figure out the other

I'm working with "Count" 1st because I think it is the more difficult 1 to work out

thanks again

Answer 2

thanks for the replies.

the total1 etc are just example names.

some of the actual names are long and I change them to shorter ones as I think of better ones

forgive my terminology errors I have been admonished about that before but this is my 1st and last Access adventure

It started as a whim and has become a quest.

I will try these suggestion

thank you

Answer 3

To count those with values try:

IIF([Total1] IS NULL,0,1) + IIF([Total2] IS NULL,0,1) + IIF([Total3] IS NULL,0,1) …..and so on

Answer 4

As Gina said, the Nz() function is probably what you need. But calling 12 queries and using field names like you describe, also points to questions about your design.

Answer 5

Hmm, well first you must mean *fields* because only Excel has *Cells*.  That said, are you trying to Count or Sum?  Because if you are trying to Sum then...

MyTotal: Nz([total1],0)+Nz([total2,0)+ etc...

...should do it.

Side note: I always worry when I see field names like *total1, total2, etc... that you have your Table set up correctly.  Can you tell us a little about what you are doing?