Using the Nz Function in a query

[JulieS]

My query calculates the count of a Cost field for a number of categories (ie. >10, 10-99, 100-150) for a specified year. Each category comes from a different query. If one of the categories returns null, the entire query does not return any value.

What I would like to do is use the Nz function to return 0 if the Count is Null. The Nz example in the help file says to put the function in the Field row of the query, but the problem when I put it there is that I am unable to choose a query in the Table row (as it becomes an expression).

It might be easier to code this right in SQL rather than the query design, but I have no idea how to do this either.

Thanks,
Julie

 

[jackal63]

when your putting the name of the field in the top row. Instead of entering "FIELD" and putting "TABLE" in the table row below, put "EXPRESSION: NZ(

.[FIELD],0)" in the field space instead. Note that EXPRESSION can be the same as the field name and this won't cause a conflict.

I've had the same problems with queries and have used NZ to fix them, hope that helps you out.

 

[JulieS]

Hmmm.. it's still for some reason returning nothing.
I still have the 'Count' function in the Totals field. could this be causing the problem?