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

Hiding field in report, when value is "0"

P: 27
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.
Jun 12 '12 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. #;\-#;;
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.

Share this Question
Share on Google+
6 Replies


100+
P: 759
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.
Jun 13 '12 #2

P: 47
I usually do it through VBA code in OnOpen Event for the Report something like
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel as Integer)
  2. Dim VisEnable
  3. If Me.Field<1 Then
  4. VisEnable=False
  5. Else
  6. VisEnable=True
  7. End If
  8. Me.FieldName.Visble=VisEnable
  9. End Sub
  10.  
Jun 13 '12 #3

100+
P: 759
@rwalle
Can you upload an example ?
Jun 13 '12 #4

P: 47
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
Attached Files
File Type: zip EjemploDb.zip (61.0 KB, 744 views)
Jun 14 '12 #5

100+
P: 759
@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).
Jun 14 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
The best trick here would be to use the textbox format.

For your textbox boud to [Gross 2] you write in the format property:
Expand|Select|Wrap|Line Numbers
  1. #;\-#;;
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.
Jun 18 '12 #7

Post your reply

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