Change Query Report is Based On

S

smh

I am running Access 2000 SP3 on Win 2K machine.
I have a database of my basketball dates and have a couple of reports that I
like the format and layout. I want to "change" the query that the report
uses. Right now I have a query to give me all the dates and locations for
the next 4 weeks. The report is Next 4 Weeks Schedule. I would like to then
do a query for Next Weeks Schedule so I can hang it on the fridge for my
wife to know where I'll be. But I don't want to have to go back and
re-format or create a new report format, just change the query that the
report uses as its basis.

Am I missing something simple here?
Any help appreciated. I don't want to have to create a bunch of different
reports and then go back and massage the column widths etc.

Thanks
SMH
 
R

Rick Brandt

smh said:
I am running Access 2000 SP3 on Win 2K machine.
I have a database of my basketball dates and have a couple of reports
that I like the format and layout. I want to "change" the query that
the report uses. Right now I have a query to give me all the dates
and locations for the next 4 weeks. The report is Next 4 Weeks
Schedule. I would like to then do a query for Next Weeks Schedule so
I can hang it on the fridge for my wife to know where I'll be. But I
don't want to have to go back and re-format or create a new report
format, just change the query that the report uses as its basis.

Am I missing something simple here?
Click to expand...

Yep, just open the report in design view and look at the property sheet's
{Data} tab. The first item listed will be RecordSource and will currently
have the name of your existing query. Just change that to a different query
and save the report.

You could also use a RecordSource query that includes all of the data and
then apply a filter when opening the report by opening it from a command
button on a form. Depends on how flexible/complicated you want to go.
 
S

Susan VanAllen [MVP]

Open the report in Design View, then go to the properties of the report
itself (upper left corner by the ruler) On the data tab, change the record
source to the new query. Make sure you have all the same fields in the new
query or it will error when you go to preview.
 
S

smh

Thanks Rick, the first way worked great, how would I use the RecordSource
Query and filter? I am not real proficient in Access.

SMH
 
V

Van T. Dinh

I would recommend using a Parametrised Query that requires the user to reply
1 or 4 (or any +ve number of weeks, really) and then the Query will select
Records for 1 or 4 weeks as per the user's selection for the Report.

Check Access Help on "Parameter Queries"
 
R

Rick Brandt

smh said:
Thanks Rick, the first way worked great, how would I use the
RecordSource Query and filter? I am not real proficient in Access.
Click to expand...

Simple example:

Report Name: SalesSummary
DateField: SaleDate

To open report and see all sales for the current month...

DoCmd.OpenReport "SalesSummary", acViewPreview,,"SaleDate > #1/1/2005#"

The final argument is structured the same as a valid WHERE clause in a query
but omits the actual word "WHERE". So you can build a query that does what
you want, then go into SQL view of that query and copy the WHERE clause to
use as your filter argument.

Since the WHERE clause needs to be inside double-quotes you might have some
other issues to deal with. If your WHERE clause includes double-quotes
within itself then those either need to be doubled up or replaced with
Single-Quotes or they will "break" the syntax of the OpenReport method. For
dates though you should be able to pretty much copy and paste the WHERE
calsue from a query.
 
Top