Returning "$0.00" for anull value

tkopari

Board Regular
Joined
Apr 30, 2004
Messages
174
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
 
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.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Why not this?

Balance: FormatCurrency([tblX]![Field1]-[tblY]![Field2]-[tblZ]![Field3])
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Tony

Nice to hear.:)

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

Forum statistics

Threads
1,212,934
Messages
6,110,760
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top