Null Value Help

[bill crumpton]

I have a report based on a query. The report sums values from the query. However, when a certain value is "0" the field on the report is blank. I would like for there to be a "0" in the field instead of a blank. Any help is greatly appreciated.

BAC

 

[Atomic Shrimp]

I think you're after the NZ function:

NZ([YourFieldName]) should do it

HTH

Mike

[This message has been edited by Mike Gurman (edited 02-01-2001).]

 

[bill crumpton]

Thanks Mike,

Where do I place that function?

BAC

 

[TGHockett]

Bill .. Mike G. is right on, and I can relate to your woes as I struggled with Acc2k reports setting null values to NOT

last year based on historical sales data, quite often blank in the mainframe for a particular model.

Mike G has the basics down right, but you will need to build an expression in your query to handle records where there is data for your report... e.g., use an Iif function to test if the data is null, if so use the NZ function to make it zero.

If you have more problems ... let us know here, and I can find a copy of the Iif() function I used for my 12 months of projected data.

TGH

 

[Atomic Shrimp]

You shouldn't need to use Iif to test for null, then convert using the NZ function, as NZ should leave any non-null values unchanged.

The exception to this (I think) is when the value returned is null not because the field is empty, but when your'e doing a Left or Right join in a query and the value returned is null because there is no matching row; I think you'd need to use the Iif that TG mentioned in that case.

To use the NZ function in your query, just type NZ([YourFieldName]) in a blank column in the FIELD row of the query design grid, Access will change this to something like:
Expr1: NZ([YourFieldName])

HTH

Mike

 

[bill crumpton]

Mike and TG
Thanks to both of you for your help. Problem solved. Have a great weekend.

BAC