Scan all queries for specific criteria text

#1

I need to scan all of my queries to look for a specific criteria text.
Any query with this criteria text I need to look at further and probably
modify.

Any straight forward way to do this?

thanks

bob

#2

On Apr 15, 12:24*pm, BobAlston <bobalst... (AT) yahoo (DOT) com> wrote:

Quote:

I need to scan all of my queries to look for a specific criteria text.
Any query with this criteria text I need to look at further and probably
modify.

Any straight forward way to do this?

thanks

bob

There may be other ways directly within Access but I use a third party
product called Find&Replace
bobh.

#3

BobAlston wrote:

Quote:

I need to scan all of my queries to look for a specific criteria text.
Any query with this criteria text I need to look at further and
probably modify.

Any straight forward way to do this?

You could use VBA to loop through the Querydefs collection, searching each

querydef's SQL property for the string you are interested in, either using
InStr or a regular expression, and if found, either modify the SQL property
in the loop, or, if the numer of queries containing this string is small,
use DoCmd.OpenQuery to open the query in design view. With a large number of
queries, you probably don't want them all open at once, so you will probably
want to write the querydef names to the debug window so you can process them
at your leisure. Another benefit to this alternative is it reduces the
danger of false positives.

Another alternative that might do what you want is this tool:
http://www.rickworld.com/products.html

#4

On 4/15/2011 11:49 AM, bobh wrote:

Quote:

On Apr 15, 12:24 pm, BobAlston<bobalst... (AT) yahoo (DOT) com> wrote:
I need to scan all of my queries to look for a specific criteria text.
Any query with this criteria text I need to look at further and probably
modify.

Any straight forward way to do this?

thanks

bob

There may be other ways directly within Access but I use a third party
product called Find&Replace
bobh.
OK. where can I find Find&Replace?

bob

#5

On 4/15/2011 11:52 AM, Bob Barrows wrote:

Quote:

BobAlston wrote:
I need to scan all of my queries to look for a specific criteria text.
Any query with this criteria text I need to look at further and
probably modify.

Any straight forward way to do this?

You could use VBA to loop through the Querydefs collection, searching each
querydef's SQL property for the string you are interested in, either using
InStr or a regular expression, and if found, either modify the SQL property
in the loop, or, if the numer of queries containing this string is small,
use DoCmd.OpenQuery to open the query in design view. With a large number of
queries, you probably don't want them all open at once, so you will probably
want to write the querydef names to the debug window so you can process them
at your leisure. Another benefit to this alternative is it reduces the
danger of false positives.

Another alternative that might do what you want is this tool:
http://www.rickworld.com/products.html

Thanks for the link to Rickworld. Never heard of this utility before.

Bob