I have a report that selects/filters records from field 'a' I want to change that to

[Mrbillknight]

I have a current report that displays all cars in a selected state. I would like to change the report to show all 'Ford's' in the database independent of the state. I look at the report view but I do not see where to change the filter.

[twinnyfo]

Reports are filtered external to the Report (although it is possible to filter a report using it’s VBA module).

However, typically, when you want to change the records displayed on a report, you change its record source (the query that generates the records).

We are glad to help you with this, but we will need a little more information on how your DB is structured and what your current report/record source is like. How is your report generated and how does your DB determine which state the cars are from? There may be ways to generate the record source programmatically so that you have a main form that selects which criteria to select/filter for your report.

Standing by to hepp.....

[NeoPa]

TwinnyFo:
However, typically, when you want to change the records displayed on a report, you change its record source (the query that generates the records).

I can't say I agree with that.

Typically, unless you want to generate extra work for yourself, you'd change the .Filter and .FilterOn properties of the Report itself. That's always simplest. Changing .RecordSource is often simple enough, but often isn't too. It's never simpler than setting the .Filter and .FilterOn properties.

[PhilOfWalton]

I suspect it may be a little more complicate.
In the first case, I would expect a group header for the States, listed alphabetically, and the makes of cars & details listed under that.
In the second case, I would expect a Car Make as the group header and the states listed under that.

As Twinnyfo says, until we see his relationship and exactly what his report looks like, we can go no further.

Phil

[twinnyfo]

@NeoPa,

Perhaps I mis-spoke, or more likely, I was unclear. When I mentioned changing the record source, what I meant was not changing the .RecordSource prorerty, but rather the query itself.

For example, the report rptCars may have the .RecordSource qryCars. My intent was not to change the record source to qryCarsByMake, but rather to change the Query Def qryCars to reflect the needs of the user. This could be done by creating/saving that query def programatically, and opening the report.

However, I do see advantages of having a generic query qryCars assigned to the Report, and filtering the report porgramatically when it is opened.

This method would definitely work with a one-size-fits-all report. However, I would think that a report designed around cars by state would be formatted differently (even if just slightly) from a report designed around cars by make. Perhaps this is why most of my reports do not use filters, as many of them are very specifically formatted--the queries behind them are very complex queries with sub-queries and sub-sub-queries and many complex calculations made along the way (maybe this is a bad design) and filtering is done at the earliest levels, so that rather than making calculations on 20,000 records, I am making calculations on 1,000 at the most.

If this report is simply a list of cars, one could easily assign a report title to the report and list the cars at teh form-level.

Again, we need more information to provide a concrete solution to the OP's question.

[NeoPa]

TwinnyFo:
However, I do see advantages of having a generic query qryCars assigned to the Report, and filtering the report porgramatically when it is opened.

Bingo. This is strongly preferable in most cases. SQL optimisation will typically catch lower-level filtering anyway so never assume that just because it's filtered at a higher level it won't be handled optimally by the SQL engine. Even Jet/ACE can manage this in most cases.

It is possible that the Report would also need to be tweaked, but in this case there is no mention of that as yet.

As a general rule the filtering approach makes much more sense. It keeps it simple, and you never want QueryDefs around the place where you don't know exactly what they do.