I have a number of queries within the same database. Is there anyway to change the criteria in one field on each of the queries all at the same time? Example: I have a state abbreviation in the "STATE" field in 36 queries. I run those to produce reports. Then I need to go in to those same queries and run the reports for a different state. Is there anyway to update the state abbreviation in all 36 queries so I don't need to go into each query and change the state field from something like "AZ" to "NY" in order to run the next set of reports? The state codes needed for the projects are never the same so rather than set up lots of different databases (one for each state), I just use one database and change the state abbreviation each time on every query. Would love a "search and replace" way to just globally change all the queries at once!
Multiple queries need to have criteria changed (1 Viewer)
- Thread starter scmitc
- Start date
My company is currently using Access 2002 and they don't have DAO library references set up. They won't let employees access to Library References to add or remove so, unfortunately, that isn't an option. But thank you for the response!
You can create a form and on that form, create a combo or list box, which ever you like to use. Populate the list or combo box with the list of your state abbreviations. You can do this by having these State abbreviations in a table or by selecting "Value" list a the type of list and then just type in the values you need seperated by a simi-colon.
Then in each of your queries, use the Builder to add a reference to your new combo box or list box at the criteria value.
You can then just open the form and select the state you want. When you run the query it will return values where the State is equal to the selected State on the form.
There are a lot of other things you can do from this form, like have a command button that will run one or more queries.
Then in each of your queries, use the Builder to add a reference to your new combo box or list box at the criteria value.
You can then just open the form and select the state you want. When you run the query it will return values where the State is equal to the selected State on the form.
There are a lot of other things you can do from this form, like have a command button that will run one or more queries.
Here's the SQL that was requested in one of the replies:
SELECT DISTINCT GGDD_PROV_AD.PROV_ADDR_SEQ_NUM AS [Prov Addr Seq Num], GGDD_PROV_AD.FRST_LN_ADDR AS [Frst Ln Addr], GGDD_PROV_AD.ADDR_CMT_LN AS [Addr Cmt Ln], GGDD_PROV_AD.CITY_NM AS [City Nm], GGDD_PROV_AD.STE_CD AS [Ste Cd], GGDD_PROV_AD.ZIP_CD AS [Zip Cd], GGDD_PROV_AD.ADDR_CNTY_NM AS [Addr Cnty Nm], GGDD_PROV_AD.PRTY_SEQ_NUM AS [Prty Seq Num], GGDD_PROV_AD.MAILNG_IND, GGDD_PROV_AD.BILLNG_IND, GGDD_PROV_AD.OFFC_IND AS [Offc Ind], GGDD_PROV_AD.MRKTNG_SUPRSS_IND AS [Mrktng Suprss Ind] INTO Addr_ph
FROM GGDD_PROV_AD
WHERE (((GGDD_PROV_AD.STE_CD)="NJ" Or (GGDD_PROV_AD.STE_CD)="NY") AND ((GGDD_PROV_AD.PRTY_SEQ_NUM)<9999) AND ((GGDD_PROV_AD.PROV_ADDR_C_DT)=#12/31/9999#))
ORDER BY GGDD_PROV_AD.ADDR_CMT_LN DESC;
SELECT DISTINCT GGDD_PROV_AD.PROV_ADDR_SEQ_NUM AS [Prov Addr Seq Num], GGDD_PROV_AD.FRST_LN_ADDR AS [Frst Ln Addr], GGDD_PROV_AD.ADDR_CMT_LN AS [Addr Cmt Ln], GGDD_PROV_AD.CITY_NM AS [City Nm], GGDD_PROV_AD.STE_CD AS [Ste Cd], GGDD_PROV_AD.ZIP_CD AS [Zip Cd], GGDD_PROV_AD.ADDR_CNTY_NM AS [Addr Cnty Nm], GGDD_PROV_AD.PRTY_SEQ_NUM AS [Prty Seq Num], GGDD_PROV_AD.MAILNG_IND, GGDD_PROV_AD.BILLNG_IND, GGDD_PROV_AD.OFFC_IND AS [Offc Ind], GGDD_PROV_AD.MRKTNG_SUPRSS_IND AS [Mrktng Suprss Ind] INTO Addr_ph
FROM GGDD_PROV_AD
WHERE (((GGDD_PROV_AD.STE_CD)="NJ" Or (GGDD_PROV_AD.STE_CD)="NY") AND ((GGDD_PROV_AD.PRTY_SEQ_NUM)<9999) AND ((GGDD_PROV_AD.PROV_ADDR_C_DT)=#12/31/9999#))
ORDER BY GGDD_PROV_AD.ADDR_CMT_LN DESC;
But to clarify on the SQL I sent...this is just ONE of many queries in this database that have the "STATE" field that needs to be updated. Each query has different fields - the only constant is the "STATE" field among them. So, currently, I have to go into each of the queries and update the "STATE" field with criteria of the new state abbreviation code.
I took the advice to create a form that has a simple combo box in it feeding from a Table that I included my state abbreviations. Then I created a test query which references the combo box in the criteria value. But I'm doing something wrong because the result is 0 records. I ran the exact same query but replaced the reference info with the actual state abbreviation and it came back with 172,811 records so I know there's data out there to pull in. Ugh! I think I'm SO CLOSE to finding a way around this manual entry. I've attached screen prints of using the reference code and when I just put in "CO" for Colorado data. If anyone can figure out what I'm doing wrong and let me know - I'd much appreciate it.
- Local time
- Today, 08:10
- Joined
- Feb 19, 2002
- Messages
- 43,368
It looks like the "not working" solution should work. Check the name of the control again.