display zero for blank records on report

[Whitney]

How can I get a report to display a zero for blank records in a query? Or get
the query to display a zero for a blank record?

I don't want to have to create a zero record on the table, as this
information is entered manually.

I have a subquery for each warehouse, then another query that sums all of
the warehouses into one query, but breaks out the totals for each warehouse.
If there is no record for the warehouse for the date range, it's blank. I use
this query to run the report.

 

[Maarkr]

did you try :

nz([fieldname],0)

 

[Whitney]

I tried that, but no luck.

I get error...The specified field '[SumOfQty_Received]' could refer to more
than one table listed in the FROM clause of your SQL statement.

This is because I have 4 subqueries, one for each warehouse, linked to this
query, so the field names are the same, but the tables are different.

I tried naming the fields, ex. Toronto: SumOfQty_Received, and just using
[Toronto] as the field name, but when I run the query it doesn't find the
Toronto field.

Is it possible to add nz([fieldname],0) to the report? If so where?

did you try :

nz([fieldname],0)

How can I get a report to display a zero for blank records in a query? Or get
the query to display a zero for a blank record?

I don't want to have to create a zero record on the table, as this
information is entered manually.

I have a subquery for each warehouse, then another query that sums all of
the warehouses into one query, but breaks out the totals for each warehouse.
If there is no record for the warehouse for the date range, it's blank. I use
this query to run the report.

 

[Klatuu]

Well there are a couple of things. If you are getting the error The
specified field '[SumOfQty_Received]' could refer to more than one table
listed in the FROM clause of your SQL statement., You can resolve that in
the query by specifying the domain.
[OneOfMyTables].[SumOfQty_Received]
Of course, using the table or query the field comes from.

As to your question reqarding putting the 0 in in the report. Yes, you can.
In fact, I consider it a better way to do it. Reports run faster when they
have no calculations in them, so I always try to do the calculation in the
report.

Now, in this case, there are two ways to do it.
One is to use the Nz function
=Nz([SumOfQty_Received], 0)

The other is to use the control's Format property. If you read up on the
Format property in VBA Help, it points out the format string has 4 sections.
1 is for positive numbers
2 is for negative numbers
3 is for zeros
4 is for Nulls
So a format of 0;(0); "Zero"; "NULL"
woud show all positive numbers as they are, all negative numbers would be
enclosed in parenthises, when the field is 0, it would display the work
Zero, and if the field is null, it will display the word NULL, so to get it
to show as 0:
0;(0);0;0

I tried that, but no luck.

I get error...The specified field '[SumOfQty_Received]' could refer to
more
than one table listed in the FROM clause of your SQL statement.

This is because I have 4 subqueries, one for each warehouse, linked to
this
query, so the field names are the same, but the tables are different.

I tried naming the fields, ex. Toronto: SumOfQty_Received, and just using
[Toronto] as the field name, but when I run the query it doesn't find the
Toronto field.

Is it possible to add nz([fieldname],0) to the report? If so where?

did you try :

nz([fieldname],0)

How can I get a report to display a zero for blank records in a query?
Or get
the query to display a zero for a blank record?

I don't want to have to create a zero record on the table, as this
information is entered manually.

I have a subquery for each warehouse, then another query that sums all
of
the warehouses into one query, but breaks out the totals for each
warehouse.
If there is no record for the warehouse for the date range, it's blank.
I use
this query to run the report.

 

[Whitney]

AWESOME!! Thank you so much. You're a life saver.
I went the easy route and used the formatting 0;(0);0;0

Well there are a couple of things. If you are getting the error The
specified field '[SumOfQty_Received]' could refer to more than one table
listed in the FROM clause of your SQL statement., You can resolve that in
the query by specifying the domain.
[OneOfMyTables].[SumOfQty_Received]
Of course, using the table or query the field comes from.

As to your question reqarding putting the 0 in in the report. Yes, you can.
In fact, I consider it a better way to do it. Reports run faster when they
have no calculations in them, so I always try to do the calculation in the
report.

Now, in this case, there are two ways to do it.
One is to use the Nz function
=Nz([SumOfQty_Received], 0)

The other is to use the control's Format property. If you read up on the
Format property in VBA Help, it points out the format string has 4 sections.
1 is for positive numbers
2 is for negative numbers
3 is for zeros
4 is for Nulls
So a format of 0;(0); "Zero"; "NULL"
woud show all positive numbers as they are, all negative numbers would be
enclosed in parenthises, when the field is 0, it would display the work
Zero, and if the field is null, it will display the word NULL, so to get it
to show as 0:
0;(0);0;0

I tried that, but no luck.

I get error...The specified field '[SumOfQty_Received]' could refer to
more
than one table listed in the FROM clause of your SQL statement.

This is because I have 4 subqueries, one for each warehouse, linked to
this
query, so the field names are the same, but the tables are different.

I tried naming the fields, ex. Toronto: SumOfQty_Received, and just using
[Toronto] as the field name, but when I run the query it doesn't find the
Toronto field.

Is it possible to add nz([fieldname],0) to the report? If so where?

did you try :

nz([fieldname],0)

:

How can I get a report to display a zero for blank records in a query?
Or get
the query to display a zero for a blank record?

I don't want to have to create a zero record on the table, as this
information is entered manually.

I have a subquery for each warehouse, then another query that sums all
of
the warehouses into one query, but breaks out the totals for each
warehouse.
If there is no record for the warehouse for the date range, it's blank.
I use
this query to run the report.

 

[Klatuu]

Glad I could help. I usually use the formatting when I can.

AWESOME!! Thank you so much. You're a life saver.
I went the easy route and used the formatting 0;(0);0;0

Well there are a couple of things. If you are getting the error The
specified field '[SumOfQty_Received]' could refer to more than one table
listed in the FROM clause of your SQL statement., You can resolve that in
the query by specifying the domain.
[OneOfMyTables].[SumOfQty_Received]
Of course, using the table or query the field comes from.

As to your question reqarding putting the 0 in in the report. Yes, you
can.
In fact, I consider it a better way to do it. Reports run faster when
they
have no calculations in them, so I always try to do the calculation in
the
report.

Now, in this case, there are two ways to do it.
One is to use the Nz function
=Nz([SumOfQty_Received], 0)

The other is to use the control's Format property. If you read up on the
Format property in VBA Help, it points out the format string has 4
sections.
1 is for positive numbers
2 is for negative numbers
3 is for zeros
4 is for Nulls
So a format of 0;(0); "Zero"; "NULL"
woud show all positive numbers as they are, all negative numbers would be
enclosed in parenthises, when the field is 0, it would display the work
Zero, and if the field is null, it will display the word NULL, so to get
it
to show as 0:
0;(0);0;0

I tried that, but no luck.

I get error...The specified field '[SumOfQty_Received]' could refer to
more
than one table listed in the FROM clause of your SQL statement.

This is because I have 4 subqueries, one for each warehouse, linked to
this
query, so the field names are the same, but the tables are different.

I tried naming the fields, ex. Toronto: SumOfQty_Received, and just
using
[Toronto] as the field name, but when I run the query it doesn't find
the
Toronto field.

Is it possible to add nz([fieldname],0) to the report? If so where?

:

did you try :

nz([fieldname],0)

:

How can I get a report to display a zero for blank records in a
query?
Or get
the query to display a zero for a blank record?

I don't want to have to create a zero record on the table, as this
information is entered manually.

I have a subquery for each warehouse, then another query that sums
all
of
the warehouses into one query, but breaks out the totals for each
warehouse.
If there is no record for the warehouse for the date range, it's
blank.
I use
this query to run the report.