My ADO Recordset Filter

I am filling a LV based on the values passed using radio buttons after update to nFilter(INT).

I am getting a mismatch and would like to figure this out. Basically what I am needing to do is compare the Archive Date Due on the column (ArchDateDue DateTime(8)) and compare them to display records that are between the given dates.

Code:

---

Private Sub FillArchiveDue(nFilter As Integer)
lvwArchiveDue.ListItems.Clear

RS(0).filter = "DATEDIFF(DD, ArchDateDue, CURRENT_TIMESTAMP) <= " & nFilter
 

    Do While Not RS(0).EOF
        With lvwArchiveDue.ListItems.Add(, "F" & RS(0)!id & "")
            .Text = RS(0)!id
            .SubItems(1) = RS(0)!id
            .SubItems(2) = RS(0)!clientid
            .SubItems(3) = RS(0)!clientname
            .SubItems(4) = RS(0)!DISC
            .SubItems(5) = Nz(RS(0)!ArchDateDue, #1/1/2000#)
            .SubItems(6) = Nz(RS(0)!ProgramNumber, 0)

        End With
        RS(0).MoveNext
        'Next row
    Loop

RS(0).filter = adFilterNone
 
End Sub

---

SO far just errors: "Arguments are of the wrong type, are out of acceptable range, or are in conflict of one another"

I believe that the interval argument has to be enclosed in double quotes. Perhaps this might help:

Code:

---

RS(0).Filter =  "DATEDIFF(""dd"", ArchDateDue, CURRENT_TIMESTAMP) <= " & nFilter

---

I've just done some Googling around and it looks like the Filter poperty does not support calculated values - the Filter is performed locally on the RecordSet rather than by the Database Engine.

It looks as if you're restricted to Filters of the form FileldName-Operator-Value so you may have to re-query with the DateDiff within a WHERE clause. Even concatinating with AND and OR has restrictions as well.

See here: http://docs.sun.com/source/817-2514-10/Ch11_ADO115.html for example

You may also want to look at the .Find and .FindNext methods.

Since the Nz() function doesn't exist in VB6 this must be an Access question.

In any case the Find method only allows searching based on a single column making it somewhat more restrictive than the Filter property.