Find Text in a Query

Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!

On Sat, 6 Mar 2010 14:10:01 -0800, PeterM
wrote:

Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!

There might be a way to use the systems tables, but I wasn't able to figure it
out. Try:


Public Sub SearchQueries()
Dim q As QueryDef
For Each q In CurrentDb.QueryDefs
If InStr(q.SQL, "Forms") 0 Then
Debug.Print q.Name, q.SQL
End If
Next q
End Sub
--

John W. Vinson [MVP]

http://www.rickworld.com/products.html

--

AG
Email: npATadhdataDOTcom


"PeterM" wrote in message
...
Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!

This seems to work:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysQueries.Expression) Like "*forms*") AND
((MSysObjects.Type)=5));

It will return "internal" queries (recordsources for forms, combo-boxes,
etc, which have a SQL statement); if you want to exclude those, use this:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like
"*forms*") AND ((MSysObjects.Type)=5));

HTH,

Rob


PeterM wrote:
Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!

John, AC and Rob... thanks for your answers, works like a champ!

PeterM

"Rob Parker" wrote:

This seems to work:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysQueries.Expression) Like "*forms*") AND
((MSysObjects.Type)=5));

It will return "internal" queries (recordsources for forms, combo-boxes,
etc, which have a SQL statement); if you want to exclude those, use this:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like
"*forms*") AND ((MSysObjects.Type)=5));

HTH,

Rob


PeterM wrote:
Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!


.

kl,,,,,,cccc

"PeterM" a écrit dans le message de
groupe de discussion : ...
Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!