ADO Form Recordset Filter

[gray]

Hi

Access 2002 BE / 2007 FE .mdb files

I've a form which I populate with an ADODB RS... I add filters to the RS as in:-

Dim Where_Str As String
 
Where_Str = "xxxx  yyyyy" etc
 
With me.form.recordset
   .filter = adFilterNone
   .filter = Where_Str
   set me.form.recordset = me.form.recordset
End With

I've done this 10x1000 times without a problem... but I can't figure why the following filter string fails...

I've msgboxed out all the fields and they do exist in the recordset... they are all declared as Longs or Integers....

I've added the filter fields progressively and they work until I add the " AND " fields... I've tried all sorts of combinations of brackets but it just refuses to work?

WHERE_STR = "[TBL1_Unique_No]=-999 OR [TBL1_Unique_No]=1259 AND [TBL1_Last_Updated_by_Unique_No]=6"

Surely, surely, surely combinations of ORs and ANDs are allowed in ADO filters?

Faliure is "Error No: 3001 - Error Decription: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Thanks in advance

 

[gray]

Hi

I've figured this out.... another stroke of genius from someone, somewhere who decided that operator precedence wasn't really their thing!

If you are combining ANDs and ORs in an ADO filter string they have to be done long-hand..... so... where in SQL you might put:-

WHERE (TBL1_Unique_No=-999 OR TBL1_Unique_No=1259) AND TBL1_Last_Updated_by_Unique_No=6

You need:-

.filter = "([TBL1_Unique_No]=-999 AND [TBL1_Last_Updated_by_Unique_No]=6) OR ([TBL1_Unique_No]=1259 AND [TBL1_Last_Updated_by_Unique_No]=6)"

Or at least that's what seems to have worked for me....