Can you call the same query with different parameters for the same report?

[lhgrappler]

Hi,

I'm sure there is a more efficient/eloquent way of dealing with my current issue. It's a small database and will work as is, but if there's a better way I'd like to learn it. I have tried to search, but nothing I'm finding matches my situation.


I have three nearly identical 2-step queries (so, technically 6) that do the same thing. Find the balance up to a date. One is Current Balance, with no date criteria, one is starting balance (with the date coming from txtStartDate on a report date range form), one is ending balance (with the date coming from txtEndDate on a report date range form)

The report has starting, ending, and current balance fields.
Is it possible (maybe with TempVars?) to call the same query, with different criteria, multiple times for one report?

Thanks

 

[theDBguy]

Hi. Yes, that should be possible. It's just like using the same table more than once in the same query.

 

[isladogs]

Yes .... but on different occasions
Just filter the report by the required fields and criteria each time

 

[plog]

I have three nearly identical 2-step queries (so, technically 6) that do the same thing...Is it possible (maybe with TempVars?) to call the same query, with different criteria, multiple times for one report?

I'm not entirely clear on your set up, but I might have an idea--DoCmd.OpenReport. It's a VBA command that will open a report, one thing you can do with it is add criteria. :

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport

So suppose I wanted to make a sales report and allow the user to choose 3 criteria to add to it---Sales Date, SalesPerson, Product. I would first create a generic query that included Sales Date, SalesPerson, Product and SalesAmount without any criteria. Next I would create a report based off that query. When opened it would show everything.

Now here's the magic. I make a form with 3 inputs where the user can select SalesDate, SalesPerson and Product criteria and a button that says "Open Report". The user would select the criteria they wanted to use (if any) and click the button. The button code would construct a filter string based on the input criteria and then do the DoCmd.OpenReport with that filter string open the report to just the data they wanted.

 

[isladogs]

You could look at the Student Target Grade Explorer example app if you didn't fully understand either of the last two replies