Searching records with multiple fields

[waldo7474]

Hi All!!

I am trying to do something very similar to Thread 702-450881. I have a database with five different lookup controls.

PartNumberLookup
DescriptionLookup
FirstUsedOnLookup
Etc.

I used the comments in Thread 702-450881 to come up with code used to make it possible for the user to use one, two, all, or none of these fields to make up their own filter of records. When I used the code (it has "or" comments associated with it) I got back results with either one of the fields OR the other. What I want is to get only the records that match both of the criteria. So I tried to put AND in the statement (in place of the OR) and it returned nothing.

I think it returning nothing because I am not using all the fields to search for criteria, thus leaving nulls. I think the filter is looking to incorporate what I have as criteria and the nulls. Thus leaving an empty set. I hope this makes sense. I have included my code down at the bottom. Hopefully this makes sense to someone and you can help me. I feel like I am so close.

Private Sub SearchBtn_Click()

    Me.FilterOn = True

    Me.Filter = search1 & " Or " & search2 & " Or " & search3 & " Or " & search4 & " Or " & search5
    
End Sub

Function search1()
    If IsNull(PartNumberLookup) Or PartNumberLookup = "" Then
        search1 = "[PartNumber] = 'xxxx'"
    Else
        search1 = "[PartNumber] Like '*" & PartNumberLookup & "*'"
    End If
End Function

Function search2()
    If IsNull(DescriptionLookUp) Or DescriptionLookUp = "" Then
        search2 = "[Description] = 'xxxx'"
    Else
        search2 = "[Description] Like '*" & DescriptionLookUp & "*'"
    End If
End Function

Function search3()
    If IsNull(FirstUsedOnLookup) Or FirstUsedOnLookup = "" Then
        search3 = "[FirstUsedOn] = 'xxxx'"
    Else
        search3 = "[FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
    End If
End Function

Function search4()
    If IsNull(OldPartNumberLookup) Or OldPartNumberLookup = "" Then
        search4 = "[OldPartNumber] = 'xxxx'"
    Else
        search4 = "[OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
    End If
End Function

Thanks in advance for all the help. This forum is GREAT!!!

 

[waldo7474]

Anyone???? Please Help!!