Filtering Records using 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 (or all) 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.

 

[FancyPrairie]

At a glance, the only thing I see wrong with it is that your functions are not returning anything. They should be declared like this: Function search1() As String

In order for your query to display results when all of the criteria match is, like you said, replace Or with And. Looks like this:

Me.Filter = search1 & " AND " & search2 & " AND " & search3 & " AND " & search4 & " AND " & search5

 

[waldo7474]

I appreciate your resonse FancyPrairie. Unfortunately when I added the "As String" to each Function it did not help. When they are all ANDs it returns an empty set. Even when there is only one field of criteria. However as soon as I change them to ORs then it returns results that meet either criteria in the two fields. Is there another way to go about this. It seems like I am so close, yet what I have is unacceptable for what I need to do.

Thanks again for all your help, hopefully you have another suggestion or two that I could try.