Find Text in a Query

[PeterM]

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 W. Vinson]

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

 

[AG]

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

 

[Rob Parker]

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]

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

PeterM

 

[joelgeraldine]

kl,,,,,,cccc