NZ functions in underlying queries for reports (1 Viewer)

[TGHockett]

I have tried to use the NZ funtion to clear up the null problem in query totals for reports (it works fine for the first field defined then doesn't work for the other fields). Have also tried basing the NZ function on the direct-table querie and another on which the report is based on an intermediate query --- any ideas will be appreciated (currently exporting the query data sheet data to Excel for now)

What am I missing?
TGH

 

[gino]

for nulls i sometimes use the iif function.
for example:

iif([yourfield]=0,0,calculation if not null)

it works very well for me.

 

[Pat Hartman]

Have you coded the Nz() function correctly?
Nz(YourField,0)
Notice that there are no quotes around the 0.

 

[TGHockett]

Thanks for the idea, Gino, and I will double check the formula, Pat. Actually an Access Guru from another dept. setup the initial function calc. and now that I think about it he had to use the tablename as well as the field in something like..

NZ([tablename]![fieldname],0]

Not sure why he used the tablename since its a groupby/sum query ... but will tryout your ideas. Thanks again.

 

[Pat Hartman]

Within a query use a dot to separate the table and field names, not a bang:
NZ([tablename].[fieldname],0]

Access automatically prefixes each fieldname with a tablename when you use the QBE grid to build a query. This is technically only necessary when you are using multiple tables in a single query and there are duplicate fieldnames. ie. tbl1.FldA and tbl2.FldA