Date Range from Query/Table?

[rbbakeriii]

I have 5 queries that pull data from some tables and then create new tables. Each of these queries requires a Start/End Date for a range of the data to be pulled. For everything to work, the same range is entered for each of these queries.

So I have a Macro that runs all of these queries and creates my tables to then pull the data into 2 final queries of the reports I need. Every time I run the Macro, it requests the [Start Date] and [End Date] for all of the 5 queries.

I'm assuming there is a way to make a table/query that runs before my queries in the macro, which asks for the Start/End date and then I can use that data for my criteria in each of the queries that is requesting the data. Can anyone help me? It's not the end of the world but I am tweaking this report to get the exact info I want, and every time I make a small change, I have to enter the data like 10x (for some reason Access normally asks me for it 2x in each query).

Thanks!

 

[Joe4]

Welcome to the Board!

You could create a one record table to store the Start/End Dates you input (can be entered via a Form). If you add one record, then set the Form to not allow new data entries, this will ensure that you will always have exactly one record (as long as data is always entered via the Form - which it should be, no one should be going in to your tables directly).

Then, you queries can use this one record table instead of criteria or parameters to get the dates it needs (without all those prompts).

 

[rbbakeriii]

Welcome to the Board!

You could create a one record table to store the Start/End Dates you input (can be entered via a Form). If you add one record, then set the Form to not allow new data entries, this will ensure that you will always have exactly one record (as long as data is always entered via the Form - which it should be, no one should be going in to your tables directly).

Then, you queries can use this one record table instead of criteria or parameters to get the dates it needs (without all those prompts).

Thanks- I have been browsing a few different forums for Access help and I liked your board the most. Glad to be a member now! I'm a bit of a newbie to MS Access as I just started using it last March. I have taught myself through forums/YouTube.

Once I have that table, what do I put in the criteria for queries? Can I reference a table/field in the criteria? Right now my criteria read "Between [Start Date] And [End Date]"

 

[Joe4]

You can add the table to your query, with or without joining it to the other table (since it is just a one record table, a Cartesian product that results from two table with no joins is just the same number of records). You can then reference those fields from the one record table in the Criteria section of your date fields in the other table.