Converting Null to Zero 1

[jenni]

I have a query which counts certain fields per a given criteria. When nothing is found, I recieve an empty field, or a Null field. I would like that it returns a zero in this case. I have read many of the threads which exist on this topic and looked up info in handbooks. It seems that this NZ function would work, but I have NO idea where do I write these expressions and how do I write these expressions? I am not a programmer. Thank you for yor help.

 

[ghubbell]

You should be able to use this in the query its self: If you have a column of widgets that you are counting, change the queries field name from simply [widgets] to:
ZeroWidgets: Nz([widgets],0)
Any record with a null widget value should now be turned to a zero and your totals should total. Gord
ghubbell@total.net

 

[jenni]

Thank you sooooo much! I have been struggling with this all day. Now I can enjoy my weekend! Your a saint!

 

[LarryDeLaruelle]

Gord:

I've been looking for a solution to this for a looooong time. Hopefully this is it.

I tried it just now on a crosstab query, but still get nulls where there are no values.

I entered it just as you have it above (of course with my values) as:

Year: Nz([Period],0)

Will this not work in cross tabs or am I doing something wrong?
Larry De Laruelle
larry1de@yahoo.com

 

[ghubbell]

1-What is the [Period] Value?
2-If its a crosstab can you perform the Nz at an earlier point?
I never tried directly in a crosstab... Gord
ghubbell@total.net

 

[LarryDeLaruelle]

Gord:

Period is derived from a date part (YYYY) function in the underlying query base on a referral date.

The crosstab provides a count of the number of referrals we have received from various agencies during a specified year. Period is the column header and Agency is the row Header.

I get nulls if an agency did not make any referrals in a particular year and this is what I would like to change to zero since I export the result to Excel to create charts/graphs. Excel does not like empty cells.

Thanks for the quick response.
Larry De Laruelle
larry1de@yahoo.com

 

[ghubbell]

Are you surrounding the datepart extraction with the Nz or are you running it in a column beside? (Still puzzled here...) Gord
ghubbell@total.net

 

[jenni]

Just to give you a tip. What Gord said works, the only thing that I had to adjust was instead of ","
I had to put a ";". Then it worked. Try this out.