Using Nz in a Totals Query

R

rgrantz

I have a query used as recordsource in a subreport, and it has parameters
from an open form for the DateField (so the DateField has "Where" chosen in
the Totals row). It also has the OrderedItems field at "Count," bewcause
the query is to return count of this in the given Date range. However, I
also need to have the DateField showing in the query results, becase I use
the field for grouping in the subreport/report. When I have the DateField
showing in the query design grid, I get a Null if there are no records
returned for that date range. I need to get a 0 instead, but trying to use
Nz for OrderedItems OR Date won't work (I get syntax error for
Count(Ordered.[Nz([OrderedItems],0)]

It appears I can't use Nz in a Count column in the query grid. How can I go
about doing this? The subreport right now doesn't show anything for the
control or its label if there are no records in that group's date, and even
using =Nz([CountofOrderedItems],0) as the control source of the report's
field returns the #ERROR in print preview. How can I get "0" to show in
this subreport's control when the recordsource query has no records
returned?

Thanks for any help
 
M

MGFoster

rgrantz said:
I have a query used as recordsource in a subreport, and it has parameters
from an open form for the DateField (so the DateField has "Where" chosen in
the Totals row). It also has the OrderedItems field at "Count," bewcause
the query is to return count of this in the given Date range. However, I
also need to have the DateField showing in the query results, becase I use
the field for grouping in the subreport/report. When I have the DateField
showing in the query design grid, I get a Null if there are no records
returned for that date range. I need to get a 0 instead, but trying to use
Nz for OrderedItems OR Date won't work (I get syntax error for
Count(Ordered.[Nz([OrderedItems],0)]

It appears I can't use Nz in a Count column in the query grid. How can I go
about doing this? The subreport right now doesn't show anything for the
control or its label if there are no records in that group's date, and even
using =Nz([CountofOrderedItems],0) as the control source of the report's
field returns the #ERROR in print preview. How can I get "0" to show in
this subreport's control when the recordsource query has no records
returned?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't use Nz() on the column you have to use it on the complete
table.column expression:

Count(Nz(Ordered.OrderedItems,0))

Anyway, Count() will return zero or the number of items counted, it
doesn't return NULL - unless there are NO rows matching the criteria;
then the result set is NULL. If the result set is NULL (nothing
returned) you can use the Report's NoData event to quietly close the
report, instead of opening the report w/ "#Error" in the controls.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiYj/4echKqOuFEgEQJwiACgy0ViCfvDX4dhEAwgHQ46RTUAx3MAni38
goUuJDLApibFKU2QHYdHf0Hf
=SjD4
-----END PGP SIGNATURE-----
 
J

John Vinson

I have a query used as recordsource in a subreport, and it has parameters
from an open form for the DateField (so the DateField has "Where" chosen in
the Totals row). It also has the OrderedItems field at "Count," bewcause
the query is to return count of this in the given Date range. However, I
also need to have the DateField showing in the query results, becase I use
the field for grouping in the subreport/report. When I have the DateField
showing in the query design grid, I get a Null if there are no records
returned for that date range. I need to get a 0 instead, but trying to use
Nz for OrderedItems OR Date won't work (I get syntax error for
Count(Ordered.[Nz([OrderedItems],0)]

It appears I can't use Nz in a Count column in the query grid. How can I go
about doing this? The subreport right now doesn't show anything for the
control or its label if there are no records in that group's date, and even
using =Nz([CountofOrderedItems],0) as the control source of the report's
field returns the #ERROR in print preview. How can I get "0" to show in
this subreport's control when the recordsource query has no records
returned?

Thanks for any help

The problem is that when there are no records, the value of
OrderedItems isn't zero nor is it NULL - there's simply no field
there.

Can you create a Query joining this table to the Items table with an
outer join, so that you see all items regardless of whether they've
been ordered or not? The Count should then return 0 for items which
exist but have no orders.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top