Form filter coding

[Guest]

I'm using the below code to filter form data. As you can see, the filters
allow me to filter on 3 different fields but not simultaneously.

How could I alter the code below to allow me to apply more than one filter
at the same time?

Private Sub Combo20_Change()
Me.Filter = "sCliNum='" & Me!Combo20 & "'"
Me.FilterOn = True
Me!Combo24 = ""
Me!Combo26 = ""
End Sub


Private Sub Combo24_Change()
Me.Filter = "MonthEnd=#" & Me!Combo24 & "#"
Me.FilterOn = True
Me!Combo20 = ""
Me!Combo26 = ""
End Sub

Private Sub Combo26_Change()
Me.Filter = "sControlNum='" & Me!Combo26 & "'"
Me.FilterOn = True
Me!Combo20 = ""
Me!Combo24 = ""
End Sub

 

[Allen Browne]

You want to combine the values from whichever combos have an entry, and
filter based on the combination.

This example checks if each combo is null, and builds up a string to apply
to the Filter of the form. It tacks an " AND " on the end of each one so you
can combine as many as you need, and then has to remove the last trailing "
AND ". Instead of repeating the same code in the events of the other combos,
you can just call the same code:

Private Sub Combo20_Change()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.Combo20) Then
strWhere = strWhere & "(sCliNum='" & Me!Combo20 & "') AND "
End If

If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "(MonthEnd=#" & Me!Combo24 & "#) AND "
End If

If Not IsNull(Me.Combo26) Then
strWhere = strWhere & "(sControlNum='" & Me!Combo26 & "') AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
Me.FilterOn = False
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

Private Sub Combo24_Change()
Call Combo20_Change
End Sub

Private Sub Combo26_Change()
Call Combo20_Change
End Sub

 

[Guest]

Thanks Allen... It ALMOST works perfectly. I can use the filters in any
order and combination except when I try to set combo20 with the other two
filters null. I get the following error:

Syntax error in date in query expression '(sCliNum='ALIQ') AND (MonthEnd=##)
AND (sControlNum=")'.

 

[Allen Browne]

Okay, you have the basic thing working; time to fine-tune.

I have assumed from your previous example that:
a) sCliNum and sControlNum are Text type fields.
b) MonthEnd is a DateTime type field.
c) All 3 are unbound combos.

Now it appears that these combos have somehow been assigned a zero-length
string, either through code or as the Default Value. You could fix this
problem by:
- removing the Default Value, or
- assigning Null instead or "", or
- modifying the code to test for both "" and Null in each case, e.g.:
If Not (IsNull(Me.Combo20) OR Me.Combo20 = "") Then

 

[Guest]

Allen,

I modified the code as you suggested. Works great!