Open multiple queries, change date range, run and close

[vickimurray]

Using Access 2010, I have a number of queries used in sub-reports for my master report. At the beginning of the month, I need to open each query, change the date (it will be the same date range for all of the queries) criteria(s), run the query, close it and then move on to the next query and do the same thing. Since I have over a 100 queries to accomplish the massive reporting involved, I really don't want to do this individually. Anyone have any ideas? I've tried to figure out how to do this with a macro but have had not luck in working through it so far. About have the queries use a "between" date range and the other half are grouped by month so they use a "month year" range. Most of the queries have multiple criteria so the date criteria might be used up to 10 times in one query.

[pbaldy]

I'd create a form for the user to enter the desired criteria, and have all the queries point there:

Between Forms!FormName.FromDateTextbox And Forms!FormName.ToDateTextbox

[vickimurray]

I'd create a form for the user to enter the desired criteria, and have all the queries point there:

Between Forms!FormName.FromDateTextbox And Forms!FormName.ToDateTextbox

Sorry that I did not mention that I a TOTALLY new to the Access macros. I've just finished teaching myself how to build the reports and accomplished that but now I want to figure out how to automate the updating each month.
I was assuming that I would have to set the date range as variables but that is about the extent of my knowledge, especially with this Macro Designer. That said, I think I have the theory of what you are talking about but do not have a clue of where to begin or how you would "point" a query to the form? or where the date criteria would be stored from the form. Am I just trying to bite off too much at this point?

Oh, and "yeah John Galt"!!!!

[orange]

There is a detailed tutorial (vba) here
http://www.fontstuff.com/access/acctut19.htm

[pbaldy]

how you would "point" a query to the form? or where the date criteria would be stored from the form. Am I just trying to bite off too much at this point?

Oh, and "yeah John Galt"!!!!

What I posted would be your new criteria, instead of

Between #8/1/12# And #8/31/12#

The dates aren't "stored" anywhere, they're just sitting on the form available for the query to look at.

John rocks!

[vickimurray]

Thanks! I was going to ask where to find some good tutorials and/or reference books. I'll check this out for sure.

[vickimurray]

OK, I'll work on that. Any idea on what I would use when the criteria is "July 2012" for the month summary criteria?

[pbaldy]

You could either have the user type that in, or provide month and year combos with a hidden textbox that combines the two selections to result in the desired format.

[vickimurray]

I tried this. As far as I can see it is the same as entering a Query Parameter (e.g.: Between [Start Date] And [End Date] ) which I thought before was going to be my answer by using the same Parameter names in all the queries; however, when I tried that, every time I open and update each of the queries, I have to type in the starting and ending dates... trust me, this gets old VERY fast with as many queries as I have to update! Is there a way that I can set a variable to the parameter name that is used across multiple queries so that I only have to type in the start date and the end date one time?

[orange]

Can you show us what you tried exactly?

Perhaps you could show us the SQL for a few of these queries, and identify what parameters are required.
That way we can see some actual data rather than guessing at what you have.

Sounds like you could have a form as Paul suggested, and a button to run a number of queries, sequentially, after inserting the appropriate parameters in a BETWEEN.....

[vickimurray]

Being new to the forum, could you tell me how to show you the SQL???

[orange]

I don't have acc2010, but if you select a query; go to the query design window and select SQL view, it will show the sql of your query.
You can copy and paste the sql into this forum.

[vickimurray]

This is a screenshot of my query currently:

CompanyName is text; Activity Date is a date field; all others are Yes/No fields

First I tried

1. creating a form named “FromDateTextbox” with a textbox (named also “FromDateTextbox”)
2. creating a form named “ToDateTextbox” with a textbox named “ToDateTextbox”
3. went to query and set criteria (using Builder) to

Between [Forms]![FromDateTextbox]![FromDateTextbox] And [Forms]![ToDateTextbox]![ToDateTextbox]

Results: Popped up an error message:
“This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the express to variables.”

Then I tried:

1. Typed directly into criteria: Between Forms!FormName.FromDateTextbox And Forms!FormName.ToDateTextbox
2. Which was changed to: Between [forms]![FormName].[FromDateTextbox] And [Forms]![FormName].[ToDateTextbox]

Result: popped up a dialog box for a parameter value:
, entered value, 2^nd dialog box: , entered value.

This worked but it’s the same as just typing in a criteria “ Between [Start Date] And [End Date]
When I tried that previously for all the “Between” queries, I had to enter the date every time… I have this criteria for 72 queries so I would like to find some form of assignable variable or find out how I’m creating the form Paul suggested incorrectly (as I am sure it is me, not Paul's solution, that is the problem)
Does that help?

[orange]

Select 1 query, go to query design view, change to show SQL, copy the SQL,
paste the sql in a post.

Don't execute anything.

here's aa link showing how to get to sql view in 2007.
http://www.youtube.com/watch?v=_pE7S-lzpa4

[pbaldy]

You realize the form has to be open when the query runs? The query won't open it for you. If it is open and you get the parameter prompt, something is spelled wrong.