Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameters used together (Access2003)

    I am running 2 Reports off the same table.

    For Report 1must select 2 parameters Country and City and run.
    For Report 2 I need 1 parameter - UserID.

    Each Report has it's own button that runs it's own code.

    Is there way I can use 2+1 parameters in same query so when 2 are working - 1 is disabled and when 1 is working - 2 are disabled.

    I am using [Forms]![Reports]![cmbR] or [Forms]![Reports]![cmbR] is null.

    Thanks

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

    Re: Parameters used together (Access2003)

    I'd use a single report, based on a query without parameters, and use the code behind the buttons to open the report with the appropriate selection criteria. I don't know the field types and parameters that you are using now, but the code would look like this:
    <code>
    Private Sub cmdOpenReport1_Click()
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, _
    WhereCondition:="Country = " & Chr(34) & Me.cmbCountry & Chr(34) & _
    " And City = " & Chr(34) & Me.cmbCity & Chr(34)
    End Sub

    Private Sub cmdOpenReport2_Click()
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, _
    WhereCondition:="UserID = " & Me.UserID
    End Sub
    </code>
    Warning: you won't be able to use this code literally - it's just an example!

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters used together (Access2003)

    Thank you so much, it is much pretier than what I am using now.
    Great!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters used together (Access2003)

    Report isw working and I am adding the 3rd Report to run off the same table
    I have a table and I had extracted 10 queries and each has [Forms]![Reports]![cmbR] or [Forms]![Reports]![cmbR] is null per field that is parameter.

    So my queries now looks like this;

    [Forms]![Reports]![cmbCity] or [Forms]![Reports]![cmbCity] is null AND
    [Forms]![Reports]![cmbCountry] or [Forms]![Reports]![cmbCountry] is null.
    for Report1 and

    [Forms]![Reports]![cmbID or [Forms]![Reports]![cmbID ] is null.
    for the Report 2

    [Forms]![Reports]![cmdManager] or [Forms]![Reports]![cmdManager] is null AND
    [Forms]![Reports]![cmbCountry]or [Forms]![Reports]![cmbCountry] is null.
    for Report3

    My question is - if Report 1 and 3 can use their parameters only together not one at the time but only 2 at the time - should I be using portion
    or [Forms]![Reports]![cmbCity] is null ??? Because there is no option IF blank - select ALL records.
    I will add error message 'You must select from both fields'.

    To top the cake with cherry -
    I have 10 queries and 10 SUB Reports created off this same table and each query gets all of these parameters mentioned above inserted.
    It worked so far for 2 Reports with 2+1 Parameters.
    I hope it'll work for third Report as well.

    I am still however wondering if there is a smarter way doing it. Thanks

    Yes, every Report has it's own button and code behind it for each partucular Report.
    I just did not want to multiply 10 queries by 3 to have each group having it's own parameters.

    Please, advise.

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

    Re: Parameters used together (Access2003)

    The method I described in my previous reply uses a single query, there's no need to create separate queries for each condition.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters used together (Access2003)

    Hans, it looked so pretty but...

    As I said my Main Report based off the Main table.
    I had to create 10 SUB Reports and each using the query extracting data from the Main table with filters.
    Select * from Main where Region="East"
    Select * from Main where Region="West"
    Select * from Main where Region="Central"

    So when I am using your code it does affect Main table that Main Report based off of but it does not affect SUB Reports because SUB Report's queries are not involved.

    I was about to use
    Form! FormName!Form cmbControl or Form! FormName!Form cmbControl is null
    in each of those queries to make them filter data as well and this is how question about using 3 sets of parameters came up.

    Please, advice.

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

    Re: Parameters used together (Access2003)

    It's becoming too complicated for me. I don't think I can help you with this any more, sorry.

Posting Permissions

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