Problems with Recordset Filter

Question

I'm having trouble with a filter on an ADO Recordset in legacy ASP Classic code, and I'm trying to understand if what I'm trying to do is not supported, or if I'm just doing it wrong.

I have a recordset of Items, and they have a Status of 1 (active) or 0 (inactive), and an optional End_Date. In my administrative user interface, I have a control to show all items or only those that should be displayed to end-users: Status = 1 AND ( End_Date is null OR End_Date > Date() )

To implement that logic, I tried:

rs.Filter = "Status = 1 AND ( End_Date = null OR End_Date > #" & Date() & "# )"

but I get

ADODB.Recordset (0x800A0BB9)
Unknown runtime error

After much fooling around, it seems that ADO doesn't like the grouping parens around the End_Date conditions in combination with the AND condition. If I take the parens out, this works:

rs.Filter = "Status = 1 AND End_Date = null OR End_Date > #" & Date() & "#"

But that's just an accident -- it looks like the filter conditions are evaluated in order, and so I get the results I want. If I change the AND to OR, the parens work:

rs.Filter = "Status = 1 OR ( End_Date = null OR End_Date > #" & Date() & "# )"

But of course that logic is wrong -- it shows Active but expired items.

Strangely, if I move the conditions around, it breaks again:

rs.Filter = "End_Date = null OR Status = 1 AND End_Date > #" & Date() & "# "

crashes with the same ADODB error.

I can't seem to predict what will and won't work, and the docs I've read are very sketchy on the syntax expected (it's not pure T-SQL!), the limitations, etc. and all the examples I've seen have at most two conditions. I don't think my conditions are all that complex. Can anyone tell me if what I'm trying to do is supported, if there's a better way to do it, or point me to comprehensive docs and samples that match this kind of logic?

Thanks!

Answer 1

You may find this article to be of interest: PRB: Cannot Filter ADO Recordsets for Nulls

Answer 2

Hi Val,

I know that you are working with legacy code and probably other things are working, but how do you open the recordset in this specific page? Are you using some constant defined in adovbs.inc?

For example:

rs.Open "SELECT * FROM table1", db, adOpenStatic, adLockPessimistic

Answer 3

ADO Recordset Object Filter Property:

There is no precedence between AND and OR. Clauses can be grouped within parentheses. However, you cannot group clauses joined by an OR and then join the group to another clause with an AND, like this:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, you would construct this filter as:

(LastName = 'Smith' AND FirstName = 'John') OR

(LastName = 'Jones' AND FirstName = 'John')

So you would have to construct your filter like this:

rs.Filter = "( Status = 1 AND End_Date = null ) OR ( Status = 1 AND End_Date > #" & Date() & "# )"