ADO-filter question

[Marc Vanzegbroeck]

Hi,

Does anyone know how to put a filter on a recordset with a compare between 2 fields?

Those 2 filters are working fine

Code: Select all  Expand view

oRs1:filter:="minstock < 0"

Code: Select all  Expand view

oRs1:filter:="stock < 0"

but when I do

Code: Select all  Expand view

oRs1:filter:="stock < minstock"

I get an error

Just for testing, i tried oRs1:filter:="0 > stock", and also have an error. Is it only possible to fut a fieldname a the left-site of a comparison?

[Enrico Maria Giordano]

ADO filter method allows only one column name in the filter expression, as far as I know.

EMG

[Marc Vanzegbroeck]

Enrico,

Thanks for the information. That's what I found out after searching for ADO-filters.
I guess I have to create a new recordset with a filter-condition.

[Rick Lipkin]

Marc

Here is a sample of a common multi-field filter I use...

Rick Lipkin

Code: Select all  Expand view

cFilter1 := "[iid] like '"+cStockno1+"%' and [itype] like '"+cType2+"%'"
cFilter2 := "[d] like '%"+cDesc1+"%' and [itype] like '"+cType2+"%'"
cFilter3 := "[Bin] like '"+cBin1+"%' and [itype] like '"+cType2+"%'"

oRs:Filter := cFilter1+" and "+cFilter2+" and "+cFilter3
 

[Marc Vanzegbroeck]

Rick,

I also use filters with multiple fields. But you can only compare a field with a fixed value and not with another field.

You can use

Code: Select all  Expand view

oRs:Filter := "Field1 = 0 AND Field2 = 0"

but not

Code: Select all  Expand view

oRs:Filter := "Field1 > Field2"