Access 2010 filter a form using mulitiple combo boxes working together and independently

[Wingfoot]

Hi

Please could sombody help me with these 2 related queries:

1. I have a form which can be filtered by Name and Date using two combo boxes, the Date combo box filters based on the selection of the Name combo box. This is fine apart from if I just want to filter by date, in which case it won't let me. Please could someone let me know how to get these two combo box filters working both independently as well as together. See code below that I am using so they work together:

Private Sub Combo95_AfterUpdate()
If IsNull(Me.Combo95) Then
'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[PO Type Name] = '" & Me.Combo87 & "' AND [Booking Date] = #" & Me.Combo95 & "#"
Me.FilterOn = True
End If
End Sub

2. This is a strange one - for some reason although my date filter works fine for some dates, it doesn't seem to work for others e.g. my date range is currently from 01/11/2014 - 30/11/2014. The filter works when I select 11/11/2014 or above but doesn't work for earlier dates in the range even though those dates are present in my table. Would anybody know why this would be and be able to help me?

Many thanks

 

[Head Case]

Hello Wingfoot,
1. Currently the combo boxes are not acting independently because you refer to them both in the Combo95 filter:
Me.Filter = "[PO Type Name] = '" & Me.Combo87 & "' AND [Booking Date] = #" & Me.Combo95 & "#"

Move the Combo87 filter criterion to that control. Then each should behave independently, and can act together by applying a filter to each.

2. Although there are records prior to 11/11/2014 your current filter means you also need to consider whatever is selected for PO Type Name. Are the prior records associated with a different PO Type Name to that currently selected?

With kind regards

 

[Wingfoot]

Hi Head Case

Thank you for your reply.

Regarding 2. - I've managed to solve that one by adding the following formatting to my code:

Format(Me.Combo95, "\#mm\/dd\/yyyy\#")

As far as 1. is concerned, I initially set the two combo boxes, as you suggested, up on separate controls (see below) which enabled them to be used separately but each time I tried to filter by date, having filtered by name ([PO Type Name]), the name filter was removed. Please could you tell me what I'm doing wrong?

Private Sub Combo95_AfterUpdate()
If IsNull(Me.Combo95) Then
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Booking Date] = " & Format(Me.Combo95, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True
End If
End Sub

Private Sub Combo87_AfterUpdate()
If IsNull(Me.Combo87) Then
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[PO Type Name] = '" & Me.Combo87 & "'"
Me.FilterOn = True
End If
End Sub

Many thanks for your help.

 

[Head Case]

I wonder if the following may help, specifically post 3:
http://http://www.access-programmers.co.uk/forums/showthread.php?t=226178

That applies a filter at the (sub)form level, rather than any one control.

With kind regards.

 

[Wingfoot]

Thanks Head Case - I'll have a look at it.

 

[Head Case]

Thanks Head Case - I'll have a look at it.

And you may well manage it if I can format the link correctly! Filter a Continuous Form Subform based on 2 Independent Comboboxes - Access World Forums