By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Breaking Out A Where Clause Via Code

P: n/a
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


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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
I can see you separating the main sql statement from the WHERE and
ORDER BY clauses, not adding criteria, but I suppose that is in a part
of the code you didn't post.

I don't see what the problem is, though. You would not have "multiple
lines" in an sql statement, just a longer WHERE clause, for instance
((WHERE field1 < 100 and field2 = true) or (field1 > 999 and field2 =
false)), which your code handles OK.

Can you give an example of what exactly is not working?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.