Returning "$0.00" for anull value

[tkopari]

I hope this is an easy one. I'm writing a query that returns account numbers, the account allotment for the fiscal year, the amount they spent in two different categories and their account balance. I'm only getting data returned for those accounts that actually spent something. For those that didn't I'm getting a blank in the two spending categories and balance fields. I want "$0.00" returned in the two categories and the balance fields. How do I do it. I tried Iff(Null,"$0.00"), but that didn't work.

I'd appreciate any help anyone can give me.

Thanks!

Tony

 

[tkopari]

I got the first part straightened out using the FormatCurrency function.

I'll try to explain the second part of my last post more precisely. This is my original expression:

Balance: [tblX]![Field1]-[tblY]![Field2]-[tblZ]![Field3]

It returns a blank result if either Field 2 or Field3 = 0. When I applied the Nz function to it by creating a new column that looks like this:

Expr1: FormatCurrency(Nz([Balance],"0"))

With it I get results (with another problem), unless I uncheck the "Balance:" cloumn. If I uncheck the "Balance:" column I get a prompt for Balance. If I leave "Balance:" checked then I have two columns with the same information, one with blank fields and one with data.

The other problem ("with another problem" above) I get in the "Expr1:" column is that if either Field 2 or Field 3 = 0 I get "$0.00" for the result, even if the balance doesn't equal 0.

 

[Norie]

Why not this?

Balance: FormatCurrency([tblX]![Field1]-[tblY]![Field2]-[tblZ]![Field3])

 

[tkopari]

That's why you're answering the questions and I'm asking them. Thanks! It worked.

New Problem:
I did have to add the Nz function, however. Without it I still got blank results for the records that had 0 for Field2 or Field3. But when I add the Nz function it returns a $0.00 result if either Field1 or Field2 = $0.00.

For example here is one actual record with Nz function:

Allotment Field2 Field3 Balance
$250.00 $1.98 $0.00 $0.00


Without Nz function:

Allotment Field2 Field3 Balance
$250.00 $1.98 $0.00

 

[tkopari]

Guess what? I figured it out on my own (this last part that is). Field2 and Field3 are expressions that reference actual fields in the tables involved in the query. I used the actual fields in the "Balance:" expression, not the Field2 and Field3 expressions, so it was looking at null values. I changed the references to the names of the Field2 and Field3 expressions and now it works.

Norie: Thanks for all of your help!

 

[Norie]

Tony

Nice to hear.

It's often hard to help with these sort of things without an overall idea of the situation.