Any way to show Zero in Place of Null in a query

[deepakg27]

Hi guys,

Was just wondering, is there any way to show zero in a field where the value is NULL, in a query. Later That query is to be used in report. If some how it can be achieved in report then also it is fine.

Thanks and Regards
Deepak Gupta

[isladogs]

Create an expression in your query using Nz function Nz(YourFieldName,0)
Use that in your report as well
Or set the default value =0

[Micron]

you might want to entertain the notion of making the table field default to zero, assuming this is about a table field in a query and not a calculated query field.

[Minty]

You can format it in the report in the control properties if it's a number data type.

[deepakg27]

Hi Guys,

Extremely Sorry, just realized my question was slightly wrong. Actually I have all fields with default value ZERO and after summing up gives Zero. But under certain criteria some times no records are found by the query and there it shows blank spaces in all the displayed fields. I want those fields also to be shown as zero, if possible.

I am also not able to format it in report, cant find out where to do it. My data type is currency.

Thanks and Regards
Deepak Gupta

[Minty]

Have a read here https://support.office.com/en-us/art...4-1fe286636668 for the Format options on the report control.

[Micron]

Don't see how formatting will help. If the query result is no record(s) then there's nothing to format.
No records in the entire report or just part of it? I think the latter - so i think the answer is conditional formatting if one is bent on a "fix", but you can only make it visible via shading/borders. If a control is bound to a query and there is no data in it you can't arbitrarily assign a value after it runs. The query itself would have to be modified, using a NZ expression in every field. IMHO this is more effort than it's worth as the current situation is quite normal.

[Minty]

The formatting can be made to a put 0 in the report so that the Report Grouping sum will still work, as will an export.
I'm not sure if it's more efficient than an Nz() tbh, just an alternative method.

[Micron]

As I mentioned, I don't consider this formatting. No data = nothing to format. It's more manipulating the value displayed in a control than it is formatting. At least that's my perspective and of course, you don't have to agree.

While I think the first paragraph of the linked page supports my interpretation
You have the data in a field appear in a specific format by applying custom formats. Custom formatting only change how the data is displayed and does not affect how the data is stored in a Microsoft Access database, or how users can enter or edit data.

I am willing to learn any time from anyone. If you can point out where the page confirms that displaying data where there is none is also called formatting, then I will review it again.
Thanks.

[Minty]

Only that it's in a custom Format property.
It's a very handy function on combo's for instance where you can get it to display "Enter a Value" when it's a numeric entry, instead of leaping through hoops to prompt the user by other means.

I'm not disagreeing with you in any way, it's just an often overlooked method, that our very own wino moderator (Baldy) put me onto.

[deepakg27]

Dear Minty,

could you please explain, what kind of custom formatting I need to create in order to show 0 in the report result box, if the value is null.

Regards

[isladogs]

Deepak
Did you try using Nz as I suggested in post #2

[Minty]

Dear Minty,

could you please explain, what kind of custom formatting I need to create in order to show 0 in the report result box, if the value is null.

Regards

It's explained here https://msdn.microsoft.com/en-us/vba...ncy-data-types

But as others have said - the Nz([YourFIeld],0) may well be a better option.

[deepakg27]

Dear Ridders52,

Yes I did try it. Please refer to my post #5.

Regards

[isladogs]

Here is an example showing the use of Nz to get what you want.
The attached db contains a table and report
I've deliberately left some fields/records blank (null) so you can see that it works

No formatting used in this example

HTH