Searching within queries

G

george collins

Hi,
I have about 1500 queries in one access db and I need to find the word "RAU"
in one of the queries. It is in the criteria field. Is there an easy way
to do this? or is it just open and read each query.

Thanks so much

George
 
V

Van T. Dinh

If you want to try, you can write code to traverse the QueryDefs collection
to look at the SQL of each QueryDef. Use the InStr() function to check
wether the String "RAU" is in the SQL. If it is in the SQL, print the name
of the Query / QueryDef in the Debug window.
 
J

John Vinson

Hi,
I have about 1500 queries in one access db and I need to find the word "RAU"
in one of the queries. It is in the criteria field. Is there an easy way
to do this? or is it just open and read each query.
Click to expand...

Not builtin to Access. There are some good third-party facilities to
do this:

Free:
http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

liudan

george collins said:
Hi,
I have about 1500 queries in one access db and I need to find the word "RAU"
in one of the queries. It is in the criteria field. Is there an easy way
to do this? or is it just open and read each query.

Thanks so much

George
Click to expand...
 
T

Tom Wickerath

Here is an example of doing what Van suggested. Caution: I have not tested it extensively. Copy
and paste the following code into a new module. Make sure that you have a reference set to the
"DAO 3.6 Object Library" (Tools > References).

'******Begin Code*****************

Option Compare Database
Option Explicit

Sub SearchCriteria(strSearch As String)

Dim db As DAO.Database, qryDef As DAO.QueryDef
Dim strSQL As String
Dim intFound As Integer

Set db = CurrentDb()
For Each qryDef In db.QueryDefs
If Left$(qryDef.Name, 1) <> "~" Then
strSQL = qryDef.SQL
intFound = InStr(strSQL, strSearch)
If intFound > 0 Then
Debug.Print qryDef.Name
End If
End If
Next qryDef

End Sub

'*********End Code*****************

To use this procedure from Immediate Window (Ctrl G), enter the following command:

searchcriteria("RAU")


Tom

______________________________________


If you want to try, you can write code to traverse the QueryDefs collection
to look at the SQL of each QueryDef. Use the InStr() function to check
wether the String "RAU" is in the SQL. If it is in the SQL, print the name
of the Query / QueryDef in the Debug window.

--
HTH
Van T. Dinh
MVP (Access)

______________________________________
 
Top