Here is my query, im trying to filter items using a string but also including null values. I also give the option of searching current, discontinued, or all items. the problem is when there is a null value, the query displays that item regardless of the itemstatus im searching for. I hope this isnt too confusing. can somone help please???
If Me.ItemStatus = "1" Then
strcrit1 = "(((items.itemdiscontinued)=(False)))"
ElseIf Me.ItemStatus = "2" Then
strcrit1 = "(((items.itemdiscontinued)=(True)))"
ElseIf Me.ItemStatus = "3" Then
strcrit1 = "(not(isnull(items.itemid)))"
End If
strSQL = "SELECT [itemid], [itemname], [itemdescription], [itemsupplierid], suppliers.[suppliername], [itemmanufacturer], [itemproductnumber], [itemdiscontinued] " & _
"FROM items " & _
"INNER JOIN suppliers ON suppliers.[supplierid] = items.[itemsupplierid] " & _
"WHERE " & strcrit1 & " " & _
"AND " & Forms.frmItems.cmbSearchField & " Like '" & Me.txtSearchCriteria & "*' " & _
"OR " & Forms.frmItems.cmbSearchField & " IS NULL " & _
"ORDER BY " & Forms.frmItems.cmbSearchField & ";"