Being a lazy sort of guy, I don't want to spend too much time re-inventing
the wheel.

I am in the middle of writing code that will examine any SQL passed to it and
extract certain information. What I ultimately want to achieve is to add
additional criteria and sort orders to what is already in the SQL. So I am
looking for words like UNION as this can give more than 1 WHERE or HAVING
clause; looking for those WHERE or HAVING clauses, where the start, what the
clause is and it's length; similarly looking for ORDER BY clauses, start
position, clause and length. I dont think I need the GROUP BY clauses, but
they may prove handy as may any other reserved word clauses. I am already
extracting table & query names and field names from the SQL for other
purposes. I have written some code that works, but is far from comrehensive,
and I feel sure it must have been done before. Any code gratefull received

Phil

"Phil" wrote in message news:iaspaa$fv6$1 (AT) speranza (DOT) aioe.org...

Being a lazy sort of guy, I don't want to spend too much time re-inventing
the wheel.

I am in the middle of writing code that will examine any SQL passed to it
and
extract certain information. What I ultimately want to achieve is to add
additional criteria and sort orders to what is already in the SQL. So I am
looking for words like UNION as this can give more than 1 WHERE or HAVING
clause; looking for those WHERE or HAVING clauses, where the start, what the
clause is and it's length; similarly looking for ORDER BY clauses, start
position, clause and length. I dont think I need the GROUP BY clauses, but
they may prove handy as may any other reserved word clauses. I am already
extracting table & query names and field names from the SQL for other
purposes. I have written some code that works, but is far from comrehensive,
and I feel sure it must have been done before. Any code gratefull received

Phil


This is a function I have been using to generate a query to be used in
reports
Uses a UDF ReplaceS to replace one string with another. This function was
written in Access2 and currently runs in Access97 and Access 2010, you can
replace with the built-in version

'----------------------------------
' Setup Query "qryzReports"
'
' strSource ' Main part of query, can be a Table name, Query name or SQL
String
' strWhere ' Filter to be put into a WHERE or HAVING clause
'
Private Function SetupQuery(ByRef strSource As String, ByRef strWhere As
String) As Integer
On Error Resume Next

Dim db As Database
Dim qryMyQuery As QueryDef
Dim strSQL As String
Dim iWhere As Integer ' Positions in SQL string of Where
clause
Dim iOrder As Integer ' Position in SQL string of Order clause
Dim strWhereHaving As String ' "WHERE" if no GROUP BY clause, else
"HAVING"
Dim vObjName As Variant ' Name of the table or query if any

SetupQuery = True

If strSource = "" Then
SetupQuery = False
Exit Function
End If

Set db = currentdb
db.QueryDefs.Delete "qryzReports"
Set qryMyQuery = db.CreateQueryDef("qryzReports") ' Create query.

' Copy source SQL string

Err.Clear
vObjName = db.QueryDefs(strSource).Name
If Err.Number = 0 Then ' Source is a Query name
strSQL = db.QueryDefs(strSource).SQL
Else
Err.Clear
vObjName = db.TableDefs(strSource).Name
If Err.Number = 0 Then ' Source is a Table name

strSQL = "SELECT DISTINCTROW " & strSource & ".* FROM " &
strSource & ";"

Else ' Source is a SQL String

If "SELECT" = Left$(strSource, 6) Or "TRANSFORM" = Left$(strSource,
9) Then ' Source is a SQL String
strSQL = strSource
Else
Debug.Print "pai1SetupQuery() Source is not a SQL string." &
vbCrLf & strSource
SetupQuery = False
Exit Function
End If
End If
End If

' ********** Save Query string *********

qryMyQuery.SQL = strSQL

' Add Filter if any

If strWhere <> "" Then
iOrder = InStr(strSQL, "ORDER BY") ' Start of ORDER BY clause
in SQL string

If InStr(strSQL, "GROUP BY") > 0 Then ' Replace "WHERE" with
"HAVING"
strWhereHaving = "HAVING"
Else
strWhereHaving = "WHERE"
End If

iWhere = InStr(strSQL, strWhereHaving & " (") ' Start of WHERE
clause in SQL string

If iWhere = 0 And iOrder = 0 Then ' Add WHERE clause to end of
SQL string

qryMyQuery.SQL = ReplaceS(strSQL, ";", " " & strWhereHaving & "
" & strWhere & ";")

Else
If iOrder = 0 And iWhere > 0 Then ' Add to end of Existing WHERE
clause

qryMyQuery.SQL = ReplaceS(strSQL, ");", " AND " & strWhere &
");")

Else
If iOrder > 0 And iWhere > 0 Then ' Add to end of WHERE and
before ORDER BY

qryMyQuery.SQL = Left$(strSQL, iOrder - 4) & " AND " & strWhere
& ") " & Mid$(strSQL, iOrder, Len(strSQL))

Else ' Order > 0 and Where = 0 ' Add WHERE clause before ORDER
BY

qryMyQuery.SQL = Left$(strSQL, iOrder - 1) & " " &
strWhereHaving & " " & strWhere & " " & _
Mid$(strSQL, iOrder, Len(strSQL))

End If
End If
End If
End If

qryMyQuery.Close
Set qryMyQuery = Nothing
Set db = Nothing

' Allow user to edit query
If MsgBox("Edit Query before previewing report?", vbYesNo +
vbDefaultButton2, "Query Edit") = vbYes Then

DoCmd.OpenQuery "qryzReports", acViewDesign, acReadOnly

While Screen.ActiveForm.Name = "qryzReports"
DoEvents
Wend
End If


Exit Function

End Function