Not Print on Reports - Field/Labels with "0" Value

[lalbay]

Thanks for looking at my message and change my title to Beginning Programmer.

I have a report that has 20 fields that are chemicals. I only want to print on the report any chemical field whose value is greater than 0.

For example I may have

Benzene 2
Chlorobenzene 3
Toluene 0
Xylene 0
Aniline 4

I want on the report (which is a "form Letter" type report not columns):

Benzene 2
Chlorobenzene 3
Aniline 4

Basically not show Toluene and Xylene and not put a blank space for the field and label that are not visible (i.e. Toluene and Xylene). The fields are numeric. I am not sure if I should do this at the query level, report level, use a parameter query (not my first choice), etc. Or if this is even possible. Any help will be appreciated and if you need more information, please let me know.

Thanks!

Leigh Ann

 

[PaulBricker]

Leigh Ann, you should be able to do this at the query level. Set the Criteria for the Chemical Field to
>0
This should filter out any chemicals that are 0 so your report doesn't even have to deal with them.

Paul

 

[lalbay]

I tried to do this. And my table has 173 records and when I run the query it comes up with 0 records.

Thanks

Leigh Ann

 

[mikey69]

Are the chemicals separate records in a table, or are they fields in a single record in a table. This will have a crucial bearing on what you are trying to do. If they are separate records, then Paul's aproach should work. Post a bit more detail if you are still struggling

 

[lalbay]

Yes that is a crucial to know. They are fields in a single record in a table.

 

[PaulBricker]

Well what you will need to do is this. In the On Format event for the section your information is in on the Report (usually the Detail section) put this code for each chemical text box you have.

If Me.ChemicalTxt1 = 0 Then
Me.ChemicalTxt1.Visible = False
Else
Me.ChemicalTxt1.Visible = True
End If

As I say, you will have to write this for each chemical textbox. Make sure you substitute you textbox names for ChemicalTxt1.
I don't think you are going to like the results but give it a try and let us know.

Paul

 

[lalbay]

Okay I am probably doing something wrong, but I put this:

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.BENZENE = 0 Then
Me.BENZENE.Visible = False
Else
Me.BENZENE.Visible = True
End If



End Sub

and I am getting the message error:

The expression OnFormat entered as the event property setting produced the following error.
*The expression may not result in the name of a Macro, the name of user-defined function, or [Event Procedure].
*There may have been an error evalating the function, event, or macro

Thanks all for the help. Regardless if I can make this work I will definately be making a contribution to this great site.

Leigh Ann

 

[PaulBricker]

Double check to make sure the name of your Textbox is Benzene and not Text0 (or whatever the default name is). The Control Source is not always the Name of the textbox.

Paul

 

[mikey69]

As a general comment, you might want to think about redesigning so that chemicals are stored in a separate, but related table. Lots of reasons to do this.

 

[lalbay]

Thanks all for the help - I have decided to put the chemicals in a seperate table - I can see the advantages to this.

Thanks!

Leigh Ann