Excluding records from search results that have fields with no data

[jbroge]

I am pretty new to Access so bear with me here. I'll explain my issue the best I can since I cannot include screenshots (my 1st post here). I attached some images of my query, form, and results.

I have a search form with 12 fields. In my query I use

Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null

for each field on the search form.

I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.

But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).

The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.

Thanks for any help!

 

[llkhoutx]

Remember "empty" is not the same as Null. Also test for "empty".

I use the NZ function or <column> & "" <> "", usually the latter.

 

[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