Filtering reports dynamically

[stepone]

Hi there,

I'm about to embark on writing the front end to a data warehouse which will consist mainly of reports being run where the user has set the criteria dynamically. As far as I can see there are at least 3 ways to do this ;

1. Use criteria in the reports query which look up field values in the calling form. Eg. LIKE [forms]![frmReport]![cboDepartment] & '*'. I have used this technique in the past.

2. Base the report on a query which draws all records, then create a filter string dynamically based on the selection in the form and open the report using the filter string. E.g. DoCmd.OpenReport stDocName, acPreview, , strFilter

3. Change the query definition on the fly before opening the report by setting the SQL property of the querydef object.

I've used technique 1 in the past, but find it quite fiddly when dealing with any complex criteria. It's easy to select 'HR', for example, but if people want to see the results for 'HR' and 'Finance' it's more complicated. For this reason I am drawn towards method 2 or 3, where there is more flexibility in creating the SQL string.

Since there is likely (eventually) to be quite a lot of records in this database I'm also looking for the most efficient method. So is there anything to choose between these two, or is there another way which might be better ?

Thanks for any help,
StepOne

 

[llkhoutx]

Set the report's recordsource to a query.

Modify that query approriately with code prior to opening the report.

 

[timkeats]

Using filter dynamically

I am also trying this, and can't see why I can't set the forms Filter property with a string variable. The RecordSource property allows this, but not the Filter property. Help says that both are read/write strings.

Any ideas?

 

[boblarson]

You can set the filter, but you have to remember to use
Filter="WhateverHere"
FilterOn = True

 

[timkeats]

Using a variable

Bob,

OK, but why can't i use:

Filter = strMyFilter

The recordSource property allows this type of variable setting, but not the Filter property?

Why not?

 

[boblarson]

You should be able to, but you have to encapsulate it in quotes:

Filter = Chr(34) & strMyFilter & Chr(34)

 

[Bee]

I am also basing my reports on a query which draws all records. I want to filter this query before I run the reports. I wonder if any one can provide an attachment example please?

Thanks,
B

 

[KeithG]

below is write out of Access help

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

 

[Bee]

Where do you put this code? Is it on form/report/query?

 

[KeithG]

on the form or reports Class Module

 

[llkhoutx]

Put it in the report's Detail Section.