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

[deepakg27]

Dear Ridders52,

Thanks for your mail and sorry for not replying sooner.

I checked your example, thanks for the help. In your example I would like to point you to record #6 where everything is NULL, but in total with Nz we can get the 0 there. My requirement is to get zero at Record #6 (Payment1, Income1, Income2) cells. As these fields are NULL similarly I am also getting the data from a query where the fields or Record is NULL.

Thanks and Regards


Deepak Gupta

[isladogs]

I had deliberately left it like that so you could see how it worked
That's very easy.
Just set the default value of each field to zero as I stated in post #2
Doing that also means all the Nz functions are no longer needed
See attached

[deepakg27]

Dear Ridders52,

I agree you mentioned it earlier and even suggested a way out, but it will work only when you have 0 as the answer in the field not when you have no records and the value is NULL. I am getting my report from a Query (Union Query, which is made of 4-5 queries and some queries don't have the records to the criteria mentioned in them.

Hope I have been able to explain my point, if still not clear I can send you an example DB for your reference.

Thanks and Regards
Deepak Gupta

[isladogs]

Then use Nz(fieldname,0) instead of field name for each number/currency field in your union query.

Or, do as I did, set the default value to zero in the source tables.
You would need to run update queries on existing data to update nulls to zero.

Both approaches will work as I have already shown you.
Decide which you prefer and implement it.

If you have no records at all, add a dummy record with zero values to your union query

[deepakg27]

Hi Ridders52,

Thanks for all the support and guidance. I have been able to get the desired results. Used Nz function again in the query as suggested earlier, but don't know why it did not work last time, probably i was making some syntax error or doing something wrong.

Now everything is working and thanks for the help.

Regards
Deepak Gupta

[isladogs]

Excellent. Glad you got there in the end.

[deepakg27]

I feel the problem was with Group function. When applying NZ function with group was causing no result, what I did was converted the query to sql (with group function) and then later applied the Nz function in Sql Mode to the query and it worked.

Thought would share might help some one.

Thanks and Regards
Deepak Gupta