Hiding Zero Values (Access 2000)

Topic

New Reply

Well, I’ve spent two hours plus now trying the hard way to find the answer to my leetle question from my best sources, the QUE book and this site (PS thanks to the Excel posters that solved my date/last dilemma in the earlier stages of this project). Apparently, NO ONE wants to suppress zero values in Access. Well, I do.

Still working on that price list thing, in between putting out major explosions. I’m very, very close to producing something I won’t be embarrassed by AS WELL AS something I will be able to tinker with and make into a thing of beauty in the very near future.

Here’s what I need to do:

I’m producing a price book of fine wines. A good 10% cannot be discounted for various reasons (allocations, production, cost of transport). Everything is in place, I’ve got all the fields I need, all the calculations. My calculations refer to a field that refers to the “Net” field…giving a value of “0” if it is a net item, meaning that it cannot be discounted. To make it as clear as possible, I’m downloading data via Client Access. There is a field that is a “Yes/No” whether or not it is a net item. I have added a field to my query that equates “y” with “0”, so as not to generate a discounted price. Now I gotta hide it.
Obviously, it will not do to have “$0.00” show up in the discounted fields of my report/pricebook.

For a while, I thought “NZ” (whatever that may be, MS Help is not so darn helpful) might be the answer, but it just doesn’t make sense.

Can ya, will ya help? I will now go read all the Excel posts from the last two weeks and see if I can help others in order to distract myself from my dilemma.

Reply | Quote

Replies

It isn’t terribly clear *where* you’re trying to suppress zeros. And excuse my ignorance, but what *is* Client Access and what “price list thing” are you referring to?[indent]

---

I’m producing a price book of fine wines

---

[/indent]Does that mean a report, or what?

If you’re using the value in a calculation, you can’t “suppress” the zeros because you need them. If you just want to hide them, that’s a different issue. But we still need a further explanation of what you’re doing.

The Nz() function handles Null values (zero is NOT a null value), which isn’t even close to what you seem to be describing, so no, it won’t do you any good.

Reply | Quote

I think you might simply want a custom number format; look up the Format property in the builtin help and check out the custom formats for numbers. For example, ###0.00;###0.00;” “

Reply | Quote

how about

if [discount price] = 0 then
[discount price].forecolor = "your background color"
else 
[discount price].forecolor = "your normal font color"
end if

Reply | Quote

Similar to Jerry’s answer: Instead of using the field “Discounted” on the report, use an unbound textbox, with the following data source: “=IIf ([Discounted]>0,[Discounted],””)”. This means if TRUE show the discounted value, else show nothing (quotequote with no spaces between the quotes).

Reply | Quote