Replacing Null Value with a zero

[msprunt]

First, I am a novice to Access, but quickly learning. I have a query where I want to sum all the values in an Amount field that are between certain dates. So I have an expression, >(Date()+360) And <=(Date()+540), which grabs all the records between the dates I want and sums the values in the Amount field. This works great, as long as it finds records between those dates. If it doesn't, it returns Null.

If it returns Null, I want the Null result to be replaced with a zero. I have tried, and tried to make this work, but keep failing. Any help would be greatly appreciated.

 

[skwilliams]

In the query on that particular fields line use the Nz function. For example, I have a field called txtA. On the field line I would do this.
A: Nz([txtA],0)

I've renamed my field to A and any null values now return as zero.

 

[msprunt]

I tried what you said, but I must be doing it wrong, because it still does not work. I have attached a picture of how the query currently looks. Could you use that to help me better understand what to do?

thanks for your help!

 

[bwellbor]

skwilliams has it right. Here's a picture that should help you.

 

[msprunt]

That works, you guys are awesome! One more question. When I do this, it removes the formatting. I want it to show as currency with a dollar sign, but when I do this, it removes that formatting. Anything I can do to keep the formatting?

 

[bwellbor]

Try this:

SumOfAmount1: Format(Nz(Sum([Amount]),0),"$#,##0.00")

 

[vbaInet]

Sum(Nz([Amount],0))

 

[boblarson]

Try this:

SumOfAmount1: Format(Nz(Sum([Amount]),0),"$#,##0.00")

Or another easy one:

SumOfAmount1: FormatCurrency(Nz(Sum([Amount]),0),2)

 

[vbaInet]

Oops... I goofed there