Hiding field in report, when value is "0"

[mulchgirl]

In my report, I have 2 weight fields. Sometimes there will only be a value in the first field [GROSS] and then a "0" in the [GROSS 2] field. Rather than showing the "0" in the report, I would like the field to be blank/empty. If I change the criteria for [GROSS 2] at the query level to >0, nothing shows up on the report. Thanks for your help.

[Mihail]

Use a custom function for those controls:
= IIF([FieldName]=0,"",[FieldName])

I'm not very sure about the syntax but I'm sure about to using the IIF function. Of course, can be other (better) approaches.

[rwalle]

I usually do it through VBA code in OnOpen Event for the Report something like

Private Sub Report_Open(Cancel as Integer)
Dim VisEnable
If Me.Field<1 Then
VisEnable=False
Else
VisEnable=True
End If
Me.FieldName.Visble=VisEnable
End Sub
 

[Mihail]

@rwalle
Can you upload an example ?

[rwalle]

here is a example DB I have MS Acc 2007 it has a report that has fields where the 0 is swaped by a "" and also using conditional formating I have changed the field back color and using a check box in the form that calls the report VBA Code switch visible property of several fields

[Mihail]

@rwalle
As I expect, your code (post #3) is not an answer for the original question (and can't be).
The answer is the IIF() function (and you use correctly this function in your database).

Also you make a confusion:
(don't be wary: I think that each of us have started with this confusion at the beginning)

A form/report has not fields.
Only tables and queries have fields.
A form/report has controls.
This controls can be (or not) bound to a field (from a table or a query).
So, you can show/hide controls, not fields.

Your code show/hide an unbound control.
As far as I know, this method can't be used for bound controls (that reflect a field in records).

[TheSmileyCoder]

The best trick here would be to use the textbox format.

For your textbox boud to [Gross 2] you write in the format property:

#;\-#;;

The coding for the textbox format is automatically setup to handle different values with different formats. The setup is:
Positive numbers ; Negative Numbers, Zero, Null
And the format property above will shown positive numbers as positive numbers, negative as negative, and it will not display zeros or nulls.