Record with blank field not visible after using filter

Question

II MAKE MAKING A ACCESS DATABASE.I have a form with a subform and few combo box to use as a filter the data. when there is a blank field in the table it is not shown my coding for the filter is

Function searchcriteria()
Dim device, vlan As String
Dim task, strciteria As String


If IsNull(Me.cbodevice) Then
device = "[DEVICE NAME] like '*'"
Else
device = "[DEVICE NAME]= '" & Me.cbodevice & "'"
End If


If IsNull(Me.cbovlan) Then
vlan = "[VLAN ID] like '*'"
Else
vlan = "[VLAN ID]= '" & Me.cbovlan & "'"
End If
strcriteria = device & "And" & vlan
task = "select * from L2PORTDETAILS where " & strcriteria
Me.L2PORTDETAILS_subform.Form.RecordSource = task
Me.L2PORTDETAILS_subform.Form.Requery


End Function]

Gustav Gustav · Accepted Answer · 2016-07-26T18:09:24

It may be simpler just to set the Filter property:

Function SearchCriteria()

    ' Preset Me!L2PORTDETAILS_subform.Form.RecordSource to:
    ' "select * from L2PORTDETAILS"

    Dim Filter As String

    If Not IsNull(Me!cbodevice.Value) Then
        Filter = "[DEVICE NAME]= '" & Me!cbodevice.Value & "'"
    End If       
    If Not IsNull(Me!cbovlan.Value) Then
        If Filter <> "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "[VLAN ID] = '" & Me!cbovlan.Value & "'"
    End If
    With Me!L2PORTDETAILS_subform.Form
        .Filter = Filter
        .FilterOn = (Filter <> "")
    End With

End Function

Record with blank field not visible after using filter

1 Answers