Parameters used together (Access2003)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Warning: you won’t be able to use this code literally – it’s just an example!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote