How to change criteria in a query using a macros

[Guest]

I have aprrox 50 queries that all have one field on common (Division). I need
to run each query for each division (10 of them). Fortunately, I was ably to
write VBA code in Excel that automatically runs and down loads the queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have to stop
and go in to each individual query and change the division number to set it
up for the next run of 50. What I need is macro/code that will change the
criteria for all 50 queries in one shot. The criteria must be physically
changed in the queries. Referencing a form will not work because the excel
code runs the queries in the background.
Can anyone help?

 

[tina]

well, you can change the SQL statement of a query object using VBA code and
the Replace() function; for example:

here's my "test" SQL statement, saved as a query in my database, as

SELECT Table15.*
FROM Table15
WHERE Table15.FieldA="A";

Public Sub isChangeCriteria()

Dim str As String

str = CurrentDb.QueryDefs("Table15").SQL

CurrentDb.QueryDefs("Table15").SQL = Replace(str, """" _
& "A" & """", """" & "B" & """")

End Sub

after running the procedure, the SQL view of my query object shows

SELECT Table15.*
FROM Table15
WHERE Table15.FieldA="B";

so if you know what the criteria currently is, and what you want to change
it to, you could loop through all the queries in code and change it in all
of them. you don't mention what version of Access you're using, but i
believe the Replace() function is not available in some older versions.
you'd just have to try it and see if Access recognizes the function.

hth