How Do I Make A Blank Field In A Report Show A Value of " 0 " So That Report Calculates?

[StampMan]

Hi,
How can I make a blank field in a report show a value of Zero " 0 ", if there is no record for that field. My reports will not calculate otherwise. I thought that maybe I could achieve this through conditional formatting but I fail to see how.


Any help appreciated.

[A S MANN]

In the controlsourse propert of empty field use
=IIF(IsNull([Field_Name]),"0"," ')
This will show 0 if field is null.

[StampMan]

Thank you, are you sure though that it has =IIF at the beginning and not =IF ?

[StampMan]

The control source for the field is used (by query) so what now?

It's almost like I need to set the default value for the field as " 0 " but this option is not available in properties..............

This is what's in the control source already "Sum Of CWUSTotQty" which is a query. If the table that the query is working from has no records yet, then the report shows a blank for that field on the report. This is turn stops the report from completing the calculation because I'm guessing it doesn't know what to do with the blank field. Which is why I need it to display " 0 "

So how exactly should the control source look?

Sum Of CWUSTotQty (and then an expression here)?

[lfpm062010]

Are you using a query or table for the report?

Not recommended. If you are using a table, then you should be able to change the default value in the table design. Then you need to update all NULL value in that field to 0.

Recommended. If you are using a query, then you should be able use the "IIF" or "NZ" or others to convert the field to 0 if it is NULL.

[StampMan]

The field in the report gains it's information from a query.
The query is Sum Of CWUSTotQty, so if Sum Of CWUSTotQty is already in the control source, where do I put the "IIF" or "Nz" ?
All tables (that the query looks at) have default value of 0 anyway.
Getting frustrated! lol

Keep in mind, there is no record for this field yet, and may not be for a while, which is why I need it to report " 0 " so rest of report works.



If I create a dummy record, the report totally works even if the value is 0. I don't want to have a dummy record just to get the report to work.

[StampMan]

Please see my original post in the reports section

MOD EDIT: Threads merged.

[orange]

I would suggest using the NZ() function in your query.
More on NZ syntax and examples

Select...... Nz(yourCalculatedField,0) from yourTable.....

More on calculated fields and reports here

[StampMan]

Thanks Orange,
But where exactly is the Nz function supposed to go? Table or query? In which property?

[orange]

I would put it in the Query you are using as the recordsource. Can you post the associated sql for that query?

[StampMan]

I really appreciate your help on this Orange.
I don't believe I've done anything sql related.............
The query looks at another query if that makes sense. So what would you need to look at?

[orange]

Did you look at the NZ() syntax and examples?

What you are looking for in my view, is a means to show a 0 if the fields is empty (null).
That is the purpose of NZ. So you would include -modify the query- an NZ(yourFieldThatMayBeNull,0).

This is the same info that was suggested in Post2 any many others in your other thread????

You seem extremely hesitant to use the info you have been given.

Let me lead you through a small scenario:

You design and build a Table or several tables.
You create a query using some of these tables.
You design and build a report based on the Query.
You want to test the Report, but there's no data in any table, so there is no data for the query, so there is no data for the report.
If part of your query is to add some field values together, and there are no values, there's nothing there, yet.
You can't add nothing (null) to nothing(null)..

When you design anything, you have some test data (good and bad values) and some scenarios. You test the scenarios against your design plan(data model) and resolve/reconcile all anomalies before you build the "production" database.

Here's another example, trying to parallel the steps above:

You have some land. You design some infrastructure and streets.
You then build and finish some houses. But the houses are unoccupied.
Someone requests a report from you --What is the sum of the ages of the occupants?

There are no occupants, so there are no ages, and there is no Sum?

You need data in your tables; and occupants in the houses; to do arithmetic.

The use of NZ() is just allowing a 0 to be displayed when there is nothing/null/empty/noValue/unknown in the field.

[StampMan]

I appreciate that, but no one has told me in clear terms exactly where that NZ should be placed?
One person suggested to put it in the control source, but the control source has the query name in it.
I need to know exactly what part of the query I put it into, how it should look etc.
Sorry, but I'm a newbie at this...........

[orange]

If you have a table with a numeric field that has not yet been populated.

Create this query: NOTE: This is READONLY and will not affect any data

Select Sum(YourNumericFieldHere) From YourTableHere

Place this query in the sql view of the query wizard. Run the query and tell us what happens/results.

[StampMan]

I really am grateful for your assistance, but it's easier just to create a false record for that table with both a qty and cost of " 0 " so that the report fields populate and the rest of the report now functions truly. I can just overwrite the dummy record with true figures once that type of used product comes in.

Sorry, but I thought there would be an easy fix for this.
Thank you for trying :-)