Search for Queries that Use Specific Criteria

[BJS]

Hi,

Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.

Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.

I hope my explanation is clear.
Thanks upfront for any suggestions!

BJS

 

[qurn]

Ok man I'll help you not many people seem to be in the helping mood today.

not quite certain about the "MB" thing it's not too clear to me. For the other stuff the easiest way would be to open your quirie in desighn view. Now, in the criateria box under province colomn write

=AB OR =SK OR =ON
switch back to normal view.

accesses also excudes all null values if you do this though. if you want null values as well include
="IsNull"
or
In(IsNull)

please correct me if I'm wrong.

 

[BJS]

Thank you for offering your help qurn!

I don't think I explained myself very well.
I am looking for a way to find out in which queries I will need to modify the criteria. I know how to modify the criteria, I just need to know which queries have 'criteria' in the "Province" field.

Example: I have 60 queries
1. 35 of these queries HAVE criteria in the "Province" field
2. 20 of these queries have NO criteria in the "Province" field
3. 5 of these queries DO NOT USE the "Province" field

I want the first one (1.) a list of the 35 queries that have criteria in the "Province" field.

To accomplish this, I was thinking of a button on a form. When clicking the button, you are prompted for the "field name" (in this case, the field name is "Province"). Then some code will run (I have no idea....need help with that!) that will produce a list of query names that use the field name provided "and" where there is criteria for that field. Now that I have this list of query names, I know that these are the queries in which I need to modify the criteria for the "Province" field.

I hope this makes more sense.....not sure if it is even possible?

Thanks,
BJS

 

[BJS]

Hi...does anyone have any thoughts on this one? Thanks.

 

[Jon K]

I have attached a sample database.

You can open the form and click on the command button to update the temporary table "tblTemp" with details of queries having the Province criteria and to view the SQL statements in a report.

The ADO code used is in the On Click event of the command button.
.