By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

Hide Record On Report... Help

P: 135
On my purchase order form if I leave my "price" field blank it shows up as "$0.00" on my report. For every record that I don't have a price for I would like it to show nothing. On my report preview button on my form I have the code:

Expand|Select|Wrap|Line Numbers
  1.  If IsNull([Form_Purchase Orders Subform].Subtotal) Then
  2. [Report_Purchase Order Copy].Subtotal.Visible = False
  3. [Report_Purchase Order Copy].Total.Visible = False
  4. End If 
This works if all my PO items don't have a price, but if just one of them has a price... then all the rest show the "$0.00". What do I need to do to tell each different record to not display "price" unless there is an actual price for it?
May 16 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 489
You need to look at Conditional Formating for your field. In your report, right click on the price field and select conditional formating from the drop down menu.

May 16 '09 #2

P: 135
Wow...that was easy...thanks! I never even new about conditional formatting in Access. I wonder how many times I could have used that before? =-)
May 16 '09 #3

Expert 100+
P: 489
You know you're going to have a good day when you've learned something new!!! Glad I could help.

May 16 '09 #4

P: 135
OK, so I tried the conditional formating and it worked great! .....but....conditional formatting is perfect for hiding the "$0.00" for each individual record, yet I need a way to hide the total of my P.O. items if all the items do not have prices. If I have 5 items for my purchase order and 3 of them have prices, a dollar each, I don't want the total to show $3. I want to leave the total blank so after it is printed the total can be written in later when the prices for the other 2 items are determined. I tried using the conditional formatting with the expression [UnitPrice]<0.01 if this condition is true the text will be whited out for the total. But if just one of the records has an amount (UnitPrice field) then the expression is false. What to do?
May 17 '09 #5

P: 135
Just moving this post back to the top of the page. Please someone know how to do this. I am hoping to get this done by tomorrow. =-)
May 17 '09 #6

Expert Mod 2.5K+
P: 2,545
I don't know what names you will have given to your fields, or the details of the underlying query on which your report is based - so you are likely to have to change some names to apply the skeleton ideas below.

In your report, the section total will be a calculated field which sums the item subtotals (assuming that these are just calculated as quantity * unit price or something similar). The control source of the textbox containing the calculated field will be of the form


If you want this to be blank if one or more of the items concerned has a price of $0, you can use an IIF testing the minimum price to do so, changing the control source of the total to be

=IIF(min([Unitprice]) = 0, null, Sum([subtotal]))

As mentioned, you must use your own field names in place of the ones I have assumed above - otherwise the expression above will simply result in #Error.

May 17 '09 #7

P: 135
@Stewart Ross Inverness
Thank You! That worked great! You even had the field names correct. =-)
May 20 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.