CODEWITHSUNDEEP
ms-accessvba
Mike
Mike

Reputation: 33

Filtering Microsoft Access Subform

I have a subform populated with different tasks. I want to filter them on DUE_DT with a filter that happens automatically. Some of the records have a blank DUE_DT, which I would like to filer out. I would also like to filter out records whose DUE_DT is more than 30 days past. I tried using VB but I haven't been able to find the appropriate syntax.

Upvotes: 2

Views: 14889

Answers (3)

Jonathan Elkins
Jonathan Elkins

Reputation: 475

Here is some syntax for appplying a filter to a sub form:

Forms!MyMainForm.ASubFormOnMyMainForm.Form.Filter = "whatever your filter is"
Forms!MyMainForm.ASubFormOnMyMainForm.Form.FilterOn = True

Upvotes: 3

Fabio Pereira
Fabio Pereira

Reputation: 348

Try this in VBA, should work on every form or module you put it in:

Form_subFormName.Filter = "DUE_DT IS NOT NULL AND DUE_DT >= #" & (Date - 30) & "#"
Form_subFormName.FilterOn = True

Replace the subFormName in Form_subFormName with the name of your subform, but keep the Form_.

Upvotes: 0

HansUp
HansUp

Reputation: 97101

"I want to filter them on DUE_DT with a filter that happens automatically."

I'm not quite sure what you have in mind there, but I wonder whether that could be handled by a WHERE clause in the subform's record source query.

WHERE DUE_DT >= Date() - 30

That would be automatic in the sense that the filter would always apply.

Note you don't have to add another condition to explicitly exclude Nulls (such as DUE_DT Is Not Null) because Null is not >= Date() - 30.

Upvotes: 0

Related Questions