ADO Recordset.Filter

[dandy]

Hi,

I am getting the wrong number of records when I use the .filter on a Date field.

rsInvoice.filter="Co_code='01'"
rsInvoice.filter="Doc_date>=#01/04/2013# and Doc_date<=#31/03/2014#"

Basically the earlier filters get reset.

Is there anything that I am missing here?

Any help / suggestions highly appreciated.

dandy

[techgnome]

The filter isn't additive... so if you want to keep the first filter when you apply the second filter, you need to make is all one filter....

Filter isn't anythign magicval... it's just a text property.... like the .Text property of a text box.

Textbox1.Text = "Foo"
TextBox1.Text = "Bar"

What would you expect to be in TextBox1? "Bar" right? Filter is the same thing. Remember it keeps all of the records in it, it's simply filtering what you can & can't see.

One additional thing... check your date formats... it may be misinterpreting the first one... I know I did... use a different format, one that can't be taken in an ambiguous manner... I like to use yyyy-mm-dd format... 2014-04-01 ... April 1st, 2014....
if you use 01/04/2014 the database will interpret that as Jan 4, 2014, NOT April 1, 2014 ..... 31/01/2014 ... is only slightly less ambiguous, but only because there is no 31st month, so it's clear that's a day....


-tg

[gibra]

If you want to set both filters, then try this:

Dim sFilter As String 
sFilter = "(Cod_code='01') " 
sFilter = sFilter & " AND (Doc_date>=#01/04/2013# AND Doc_date<=#31/03/2014#)"
rsInvoice.Filter = sFilter