Hello everyone,
I am reading a query's SQL string and adding criteria to that and then
resaving it. I know, might not be the best method, but it seems to
work and there are some pros to doing it this way for us.
However, there is a small issue. The code I am posting works if the
WHERE clause is a single line (meaning no OR's).
Can someone suggest how to modify the following code so that it
accounts if the WHERE clause has multiple "criteria" lines (meaning
OR's?)
Thanks
Set dbs = CurrentDb()
Dim lngI As Long
Dim strSQL As String
Dim strWhere As String
Dim strOrderBy As String
Dim strGroupBy
On Error Resume Next
strSQL = dbs.QueryDefs("qryHealthReopenedRevised").sql
strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
strWhere = ""
lngI = InStr(strSQL, "WHERE ")
If lngI > 0 Then
strWhere = Mid$(strSQL, lngI)
strSQL = Left$(strSQL, lngI - 1)
' NEED TO ACCOUNT FOR OR's HERE and more than one since a criteria
might have multiple or lines
lngI = InStr(strWhere, "ORDER BY")
If lngI > 0 Then
strOrderBy = Mid$(strWhere, lngI)
strWhere = Left$(strWhere, lngI - 1)
End If
lngI = InStr(strWhere, "GROUP BY")
If lngI > 0 Then
strGroupBy = Mid$(strWhere, lngI)
strWhere = Left$(strWhere, lngI - 1)
End If
End If