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
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

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


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