VBA filter

[rustersen]

Hi,

This is probably a simple one, but I just cant seem to get it right...

I want to filter my subform data, to only show records where field A is a higher value than field B.

Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record. :banghead:

Whats the right way?

 

[MLUCKHAM]

Form filters are set for the entire data set and are not modified for each row. You need to change your source sql for the form to do what you want to do. I would base the form on a sql statement and then modify this statement to switch on the filter. You could use the above technique if you included an extra field in your sql data set. This field would be a boolean field of A>B. This would be either true or false. You could then filter on this new field in your form by either setting the filter for "newfield = true" (A>B) or "newfield = false" (A<= B). In your filter above A and B are changing each row which is not allowed for form filters.

 

[TJPoorman]

Just curious if this will work, try placing brackets around your fields.

Me.MySubform.Form.Filter = "[A] > [B]"
Me.MySubform.Form.FilterOn = True

 

[TJPoorman]

Though if one of the fields is a calculated field, then you cannot set your filter this way.

 

[rustersen]

You're right, B is a calculated field, so that must be the problem. Thanks!

 

[rustersen]

Form filters are set for the entire data set and are not modified for each row. You need to change your source sql for the form to do what you want to do. I would base the form on a sql statement and then modify this statement to switch on the filter. You could use the above technique if you included an extra field in your sql data set. This field would be a boolean field of A>B. This would be either true or false. You could then filter on this new field in your form by either setting the filter for "newfield = true" (A>B) or "newfield = false" (A<= B). In your filter above A and B are changing each row which is not allowed for form filters.

Excellent!! Nice clean solution.. thanks a bunch!!