Hide Record On Report... Help

[MyWaterloo]

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:

 If IsNull([Form_Purchase Orders Subform].Subtotal) Then
[Report_Purchase Order Copy].Subtotal.Visible = False
[Report_Purchase Order Copy].Total.Visible = False
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?
Thanks

[DonRayner]

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.

Don

[MyWaterloo]

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? =-)

[DonRayner]

You know you're going to have a good day when you've learned something new!!! Glad I could help.

Don

[MyWaterloo]

OK, so I tried the conditional formating and it worked great! .....but....con ditional 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?

[MyWaterloo]

OK, so I tried the conditional formating and it worked great! .....but....con ditional 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?

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. =-)

[Stewart Ross]

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

=Sum([subtotal])

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.

-Stewart

[MyWaterloo]

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

=Sum([subtotal])

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.

-Stewart

Thank You! That worked great! You even had the field names correct. =-)