Hiding certain record groups in a report? 2

[BTilson]

I am trying to make certain groups of records in a report invisible, based on an expression in the report.

The value that I am wanting to filter by is a value that
is actually calculated on the report from various fields
in the query that the report is based on. The actual
calculation that I am wanting to filter by is this:

Sum([6wkReq])-([QTY_ON_HAND]+[QTY_ON_ORDER])

It is in the group header of the report, with no detail
records being show. This is calculated for each group of
records to show how much of a certain material the company
needs to order. To my knowledge, this isn't possible to
calculate in the query due to the nature of the records.
There are many different parts that can be made from each
material, so the QTY_ON_HAND and QTY_ON_ORDER fields are
the same for each record because they reference the
quantity we have measured in feet, but the 6wkReq field
can have different values between the records due to the
fact that the imported data that we recieve is based on
the number of parts needed, and not on material.

Basically what I want to do is hide the entire record
group if the calculated value mentioned above evaluates to
zero or to a negative number, therefore meaning that we do
not need to order any of this material at this time.

I tried to apply a filter using the text box that contains the calculated value, but it doesn't seem to recognize the textbox. When I open the report, it prompts me to enter a value, when it should be using the value in the text box.

Any ideas? Hope I didn't make this too confusing.

 

[PaulBricker]

It sounds like you just need to set the visible property for your Detail Section if your expression <= 0. Something like this in the format event for the Detail Section.

If Me.CalculatedTextboxName <= 0 Then
Me.Detail.Visible = False
Else
Me.Detail.Visible = True
End If

See if that does it.

Paul

 

[BTilson]

Thanks a ton. That worked perfectly, and such a simple way to go about it too. I learn something new every day.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PaulBricker]

Glad that helped. After you've used something like that a couple times, you start to see all kinds of ways to use it with database objects like Reports and Forms.

Paul