Find Text in a Query

  • Thread starter Thread starter PeterM
  • Start date Start date
P

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!
 
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!
Click to expand...

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
 
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
 
Back
Top