Limit detail on report based on field value

[gwog]

Hi,

I am using Access '97.

I have a report that I would like to limit the detail to show only items if 1 field > 0.

I can't limit it by setting the value up in a query, because there is another field I need on the report that is calculated based on all the data. So if I limit by query the calculation is wrong.


Here is what I've tried:

In the Reports Detail Section On Format Property I have:

=IIf([Shorted]=0,Section(0).[Visible]=False,Section(0).[Visible]=False)

But is not making any difference all the records are still showing on the report.

Any sugestions would be greatly apprecaited!

Lisa.

 

[gwog]

Great News!

I figured it out!

Me.Detail.Visible = Me.Shorted > 0

Sorry to post an unnecessary question.

Lisa.

 

[GingerR]

how are you launching your report?
i suggest that you have a main form, with buttons on it.
say you are wanting to open the report with that field >0.

in the button's OnClick event, you'd concoct a WHERE string.

dim strWHERE
strWHERE = "FieldName > 0"

docmd.OpenReport "ReportName",acviewPreview,,strWhere

so the report only opens with that filter.

then put another button on the form.
set a different WHERE clause, or none at all.

you can get slicker with this: have radio buttons/option box on your form where the user will see their choice of report:

• Only Show Things Where Blah > 0
• Show All Things

user selects one or the other radio button.
then there's only ONE button on the form that says VIEW REPORT. in it's OnClick event, you put:

dim strWhere
Select Case fraReportChoice
Case 1
     strWhere = "FieldName > 0"
Case 2
     strWhere = ""
End Select

docmd.OpenReport "ReportName",acviewPreview,,strWhere

then you can use one (or few) reports, and just change the filter/where clause as needed.

 

[gwog]

Thanks Ginger.

Your suggestion sounds much more flexible than my solution. I'll give it a try.

thanks for taking the time to respond.

 

[gwog]

Hello,

OK, I have a new problem that is similiar to the 1st one I mentioned.

Now my boss wants a couple of totals on the report.

The report is grouped in two ways:

1) Category
2) Company

I need to total 3 fields for each:

DNC
ADJORDER
ADJSHORT

My problem is - as I said in the initial post - I can't run the report from a query which limits it to items that are shorted (this is all want to view on report).

I got it to only display the shorted items. But how do I get it to only sum the shorted items?

Thanks for your help!