Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict result in report (Access 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict result in report (Access 2000)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict result in report (Access 2000)

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

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict result in report (Access 2000)

    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?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict result in report (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict result in report (Access 2000)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict result in report (Access 2000)

    I think the attached version will do what you want.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict result in report (Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •