Automatically change query criteria

[Tara]

I'm not sure if this is the right place to post this, so please tell me if I
should cross-post in programming...

I need to run a report several times usiing the same query, but different
criteria. There are about a dozen different criteria and the user would like
to be able to simply push one command button and have all the reports
generated, rather than have to choose the various criteria from a combo box
12 different times. Is there a way to do this?

Thanks

 

[John W. Vinson]

I'm not sure if this is the right place to post this, so please tell me if I
should cross-post in programming...

I need to run a report several times usiing the same query, but different
criteria. There are about a dozen different criteria and the user would like
to be able to simply push one command button and have all the reports
generated, rather than have to choose the various criteria from a combo box
12 different times. Is there a way to do this?

Thanks

Well, this depends on what the criteria ARE. If they're static criteria, can
you not just include them in the report's recordsource? More details please!

 

[Tara]

Unfortunately, no, the criteria will not always be the same. SOME of the
criteria will never change, but several will change month to month.

My intial attempt actually was to put everything into one report - it's a
billing report that needs to be broken down by county and the service
rendered in that county. But I ran into a problem with some features they
wanted on the report and so we deemed it necessary to run separate reports
for each county and each service within that county.

 

[John W. Vinson]

Unfortunately, no, the criteria will not always be the same. SOME of the
criteria will never change, but several will change month to month.

My intial attempt actually was to put everything into one report - it's a
billing report that needs to be broken down by county and the service
rendered in that county. But I ran into a problem with some features they
wanted on the report and so we deemed it necessary to run separate reports
for each county and each service within that county.

Well, then I'm confused. You want variable criteria but you don't want to get
those criteria from the user? If not, where DO the variable criteria come
from?

You can certainly use a Form with multiple controls as the source of the
criteria - but it's not at all clear to me what you actually need. More
details please!

 

[John Spencer MVP]

It can be done using VBA function and modifying the criteria.

Dim StrWhere as String

StrWhere = "fieldCounty = 'Chester'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

StrWhere = "fieldCounty = 'Carroll'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

StrWhere = "fieldCounty = 'Allegany'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

Your report would not be filtered by county until you opened it and passed the
where string into it.

If you were using a combobox as the source for specifying the county, you
could use a loop to step through the values in the combobox

Dim I as Long
Dim ctlAny as Control
Dim StrWhere as String

Set ctlAny = Forms![FormName]![CmbCounties]

For I = 0 to ctlAny.ListCount -1
StrWhere = "fieldCounty = '" & ctlany.ItemData(I) & "'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere
Next I

Of course if you have more complex criteria then the strWhere would be more
complex to build.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tara]

This looks like it might work. I'll give it a shot this afternoon and let
you know.

Thanks!

It can be done using VBA function and modifying the criteria.

Dim StrWhere as String

StrWhere = "fieldCounty = 'Chester'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

StrWhere = "fieldCounty = 'Carroll'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

StrWhere = "fieldCounty = 'Allegany'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere

Your report would not be filtered by county until you opened it and passed the
where string into it.

If you were using a combobox as the source for specifying the county, you
could use a loop to step through the values in the combobox

Dim I as Long
Dim ctlAny as Control
Dim StrWhere as String

Set ctlAny = Forms![FormName]![CmbCounties]

For I = 0 to ctlAny.ListCount -1
StrWhere = "fieldCounty = '" & ctlany.ItemData(I) & "'"
Docmd.OpenReport "YourReportName",acViewNormal,,strWhere
Next I

Of course if you have more complex criteria then the strWhere would be more
complex to build.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Unfortunately, no, the criteria will not always be the same. SOME of the
criteria will never change, but several will change month to month.

My intial attempt actually was to put everything into one report - it's a
billing report that needs to be broken down by county and the service
rendered in that county. But I ran into a problem with some features they
wanted on the report and so we deemed it necessary to run separate reports
for each county and each service within that county.