Query Criteria not including null values

[shaun_za]

Hi, I have a form that has multiple textboxes in which the criteria for my query is typed into. However, any records containing null values are excluded if there is nothing in the textbox.

Criteria : Like "*" & [Forms]![Formname]![Textboxname] & "*"

Thank you!

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)

  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

'OR
'apply the sql to the form

sSql = "SELECT * FROM tblCompany WHERE " & sWhere