Restrict result in report (Access 2000)

[pilgrimswake]

I have a very useful report that works fine,i just want to improve it.In short,i want for the report to show only those products, that have the field branch > 0.It is very difficult fo me to do it, since the code i use is very powerful,but does not allow using a query.Otherwise the query might have solved the problem.
Just to explain how the code works.The Report is based on the table products.The table products contains the stock for each city, under the common name branch.
For example the stock for city 1 is branch0
the stock for city 2 is branch1
the stock for city 3 is branch2
etc etc.

Therefore by choosing the city, in the form Benchmark,and the option control office,i get
Bestand.ControlSource = ("branch" & (Forms!FBenchmark!Office - 1))
Here is the whole code in the OnOpen event of my report:


If IsNull(Forms![FBenchmark]![Office]) Then
DoCmd.Beep
Exit Sub
Else
Me![heading].Caption = Branching
Dim Bestand As Control
Set Bestand = Me![Bestand]
Dim Stueck As Control
Set Stueck = Me![Stueck]
Bestand.ControlSource = ("branch" & (Forms!FBenchmark!Office - 1))
Stueck.ControlSource = "items" & (Forms!FBenchmark!Office - 1)
End If
Me![heading].Caption = Branching


The report works fine,it is very elegant, but shows all the products,even products that have branch = 0, and this makes a
long list. How can i show only the products, that have branch> 0 ?

[HansV]

I assume that you open the report from FBenchmark using DoCmd.OpenReport. You can specify a where-condition argument in OpenReport:

DoCmd.OpenReport "rptProducts", acViewPreview, , "branch > 0"

Replace rptProducts with the name of your report.

BTW I don't understand why you can't use a query with criteria for branch.

[pilgrimswake]

It seems so easy now ! You have solved a big problem for me just with one stroke of the hand. it is wonderful.

[pilgrimswake]

Dear Hans,

I somehow cannot manage to open the report,it seems that i must define the branch once again.
Would you please look at my attached database,to see where the erorr is?

[pilgrimswake]

Yes,i only wanted to display records for which branch > 0.Therefore in the form i copied your suggestion:
DoCmd.OpenReport "rptWarehouse", acViewPreview, , "branch > 0"
However i get errors with it.If i open the report without the where condition, then i open the report but with nulls in it.
Maybe i should define the branch in the form.It depends on the option box.If for example the option is 2, then branch = branch1 etc.
How shall i define the branch in the form?

regards

[HansV]

You wrote that you only wanted to display records for which branch > 0. In the database you attached, branch = 0 for all records, so the report cannot display anything. If there are records with branch > 0, they will be displayed.

[HansV]

I think the attached version will do what you want.

[pilgrimswake]

Yes, it does what i want.The solution is quite sound and perfect.You have helped me solve a very important problem for me.I am very grateful to you.
i wish you all the best with all my heart