Handling Zero Records in Reports

[cue]

Hi All,

I have made a report in Access that uses a query for a record source
that merges the results from several other queries. I am finding that
if any of the subqueries come up with zero records, the main query that
is attached to the report comes up with zero records even if the other
subqueries do in fact have at least one record. As a result, all of
the fields on my report read 'Error'. If I do not include within the
main query the subqueries that come up with zero records, I get the
correct number of records for the other subqueries and my report runs
just fine. But I need the report to say 0 (zero) for those fields that
are bound to the subqueries resulting in no records.

I thought about setting all the fields in my report to zero as default
in cause any of the subqueries come up with zero records, but there
doesn't seem to be any option for that.

Does anyone else have any ideas.
I'd appreciate the help.

 

[Marshall Barton]

I have made a report in Access that uses a query for a record source
that merges the results from several other queries. I am finding that
if any of the subqueries come up with zero records, the main query that
is attached to the report comes up with zero records even if the other
subqueries do in fact have at least one record. As a result, all of
the fields on my report read 'Error'. If I do not include within the
main query the subqueries that come up with zero records, I get the
correct number of records for the other subqueries and my report runs
just fine. But I need the report to say 0 (zero) for those fields that
are bound to the subqueries resulting in no records.

Go back and get the query generating the required data
before you worry about the report. It sounds like you're
using Inner Joins to the "subqueries". Try using outer
joins (right click on the line connecting two queries and
change the join type).

I thought about setting all the fields in my report to zero as default
in cause any of the subqueries come up with zero records, but there
doesn't seem to be any option for that.

Once you get the query working properly, you can use a
custom Format to get a text box to display zero instead of
null. For a currrency field, you might want something like:
$#,##0.00;($#,##0.00);0.00;0.00

See Format Property in Help for all the options that can be
used in creating custom formats.

A slightly less efficient way to display 0 instead of null
is to use the expression =Nz(field, 0) in the text box
instead of just binding itdirectly to the field.

 

[cue]

Thanks Marsh,

I did what you suggested for the queries and used left joins instead o
inner joins and the queries are running perfectly . I can also mak
the null values appears as 0s in my report. However, I will be runnin
this same report on a regular basis so the fields that are null thi
week won't necesarily be null next week. Therefore, I can't us
=Nz(field, 0) instead of binding the fields because that would mea
they remain 0, doesn't it?

I tried the format suggestion:

Once you get the query working properly, you can use a
custom Format to get a text box to display zero instead of
null. For a currrency field, you might want something like:
$#,##0.00;($#,##0.00);0.00;0.00

But this did nothing becuase there was nothing in the field to format


Also, do you know if there is a way to bind two queries to the sam
report without them having to have a common field in common?

I appreciate your suggestions so far.
Please keep 'em coming! :

 

[Marshall Barton]

I did what you suggested for the queries and used left joins instead of
inner joins and the queries are running perfectly . I can also make
the null values appears as 0s in my report. However, I will be running
this same report on a regular basis so the fields that are null this
week won't necesarily be null next week. Therefore, I can't use
=Nz(field, 0) instead of binding the fields because that would mean
they remain 0, doesn't it?

No, they won't remain 0. The Nz function just returns a
zero to the text box when the field in the current record is
null. A report doesn't affect the data saved in a table.

I tried the format suggestion:


But this did nothing becuase there was nothing in the field to format.

Oh bleep. I had a mistake in there. It should have been:
$#,##0.00;($#,##0.00);0.00;"0.00"

Also, do you know if there is a way to bind two queries to the same
report without them having to have a common field in common?

Sorry, but you can't just throw two different queries at a
report and expect it to understand what you want. There are
a few things you can do to connect data, but you would have
to explain what the data looks like and how you want the
report to present that data.