retuen qry name if SQL contains certain text

[gdesreu]

I would like to search through my database and return the query names of any queries that contain specific text. Is this doable? Thanks

 

[ranman256]

i was always trying to find 'text' in a query , so I wrote this:

usage:
FindinSql "bob"

it prints all the results, in the debug space,

Public Sub FindInSql(pvFind, Optional ByVal pvQType)
'Dim db As Database
Dim qdf As QueryDef
Dim vFind
Dim sSql As String
'qtype can be..
Const kQS = 0
Const kQD = 32
Const kQU = 48
Const kQA = 64
Const kQM = 80
Const kQN = 128
Const kQX = 16
vFind = pvFind   '"tRptDefectsBlocked"


'Set db = CurrentDb
Debug.Print "-- Start qry find in:" & vFind
'Debug.Print "qry type FILTER:="


For Each qdf In CurrentDb.QueryDefs
    sSql = qdf.SQL
    qdf.Close
    If InStr(sSql, vFind) > 0 Then
       If IsMissing(pvQType) Then
          Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name
       Else
          If qdf.Type = pvQType Then Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name             ', "..FLT SRCH"
       End If
    End If
Next
Debug.Print "--End qry find"
Set qdf = Nothing
'Set db = Nothing
End Sub

 'qry types in English
Public Function getQryTypeNam(pvTypeNum)
Select Case pvTypeNum
    Case 0 '= select
       getQryTypeNam = "sel"
    Case 16 '= xtab
       getQryTypeNam = "xtab"
    Case 32 '= del
       getQryTypeNam = "del"
    Case 48 '= upd
       getQryTypeNam = "upd"
    Case 64 '= appd
       getQryTypeNam = "apd"
    Case 80 '= make
       getQryTypeNam = "make"
    Case 128 '= union
       getQryTypeNam = "union"
End Select


End Function

i added a param in case i only wanted to search in Update queries.

 

[gdesreu]

i was always trying to find 'text' in a query , so I wrote this:

usage:
FindinSql "bob"

it prints all the results, in the debug space,

Public Sub FindInSql(pvFind, Optional ByVal pvQType)
'Dim db As Database
Dim qdf As QueryDef
Dim vFind
Dim sSql As String
'qtype can be..
Const kQS = 0
Const kQD = 32
Const kQU = 48
Const kQA = 64
Const kQM = 80
Const kQN = 128
Const kQX = 16
vFind = pvFind   '"tRptDefectsBlocked"


'Set db = CurrentDb
Debug.Print "-- Start qry find in:" & vFind
'Debug.Print "qry type FILTER:="


For Each qdf In CurrentDb.QueryDefs
    sSql = qdf.SQL
    qdf.Close
    If InStr(sSql, vFind) > 0 Then
       If IsMissing(pvQType) Then
          Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name
       Else
          If qdf.Type = pvQType Then Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name             ', "..FLT SRCH"
       End If
    End If
Next
Debug.Print "--End qry find"
Set qdf = Nothing
'Set db = Nothing
End Sub

 'qry types in English
Public Function getQryTypeNam(pvTypeNum)
Select Case pvTypeNum
    Case 0 '= select
       getQryTypeNam = "sel"
    Case 16 '= xtab
       getQryTypeNam = "xtab"
    Case 32 '= del
       getQryTypeNam = "del"
    Case 48 '= upd
       getQryTypeNam = "upd"
    Case 64 '= appd
       getQryTypeNam = "apd"
    Case 80 '= make
       getQryTypeNam = "make"
    Case 128 '= union
       getQryTypeNam = "union"
End Select


End Function

i added a param in case i only wanted to search in Update queries.

Thanks, I'm getting a compile error sub or function not defined at getQryTypeNam"

 

[gdesreu]

i was always trying to find 'text' in a query , so I wrote this:

usage:
FindinSql "bob"

it prints all the results, in the debug space,

Public Sub FindInSql(pvFind, Optional ByVal pvQType)
'Dim db As Database
Dim qdf As QueryDef
Dim vFind
Dim sSql As String
'qtype can be..
Const kQS = 0
Const kQD = 32
Const kQU = 48
Const kQA = 64
Const kQM = 80
Const kQN = 128
Const kQX = 16
vFind = pvFind   '"tRptDefectsBlocked"


'Set db = CurrentDb
Debug.Print "-- Start qry find in:" & vFind
'Debug.Print "qry type FILTER:="


For Each qdf In CurrentDb.QueryDefs
    sSql = qdf.SQL
    qdf.Close
    If InStr(sSql, vFind) > 0 Then
       If IsMissing(pvQType) Then
          Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name
       Else
          If qdf.Type = pvQType Then Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name             ', "..FLT SRCH"
       End If
    End If
Next
Debug.Print "--End qry find"
Set qdf = Nothing
'Set db = Nothing
End Sub

 'qry types in English
Public Function getQryTypeNam(pvTypeNum)
Select Case pvTypeNum
    Case 0 '= select
       getQryTypeNam = "sel"
    Case 16 '= xtab
       getQryTypeNam = "xtab"
    Case 32 '= del
       getQryTypeNam = "del"
    Case 48 '= upd
       getQryTypeNam = "upd"
    Case 64 '= appd
       getQryTypeNam = "apd"
    Case 80 '= make
       getQryTypeNam = "make"
    Case 128 '= union
       getQryTypeNam = "union"
End Select


End Function

i added a param in case i only wanted to search in Update queries.

Nevermind it works perfectly, (It helps to put the second function there) My bad...red cheeks. Thanks this is perfect absolutely perfect for what I need.