Form Search to find all records with value in subform (regardless of field value in)

[user9]

After searching the forum, I haven't found any questions/answers quite like mine.



I have a main form with four different search fields - last name, city, phone number, and ID. I want to have a search of any of these fields (or combination of these fields) to find all records in the subform with a matching value - regardless of which field that value is in (i.e. there are multiple address fields in the subform, so the city could appear in any of these).

Additionally, if a Last Name AND City are entered I only want to return records in the subform that include both values.

Thank you in advance!

[ItsMe]

I use the following code or something similar to create search forms. You can use wildcards to allow for beginig of field, middle of field search, etc. This example assumes there will always be a value in Field1.

If Not IsNull(Me.Field1) Then
    strWhere = "[WorkOrder] LIKE '" & Me.Field1 & "'" ' a wild card is not used here. field must match exactly
Else
Exit Sub
End If
If Not IsNull(Me.Field2) Then
    strWhere = (strWhere & " AND ") & "[Container] LIKE '" & Me.Field2 & "*'"
End If
If Not IsNull(Me.Field3) Then
    strWhere = (strWhere & " AND ") & "[PO] LIKE '" & Me.Field3 & "*'"
End If

[lfpm062010]

You can modified the "Row Source" after you have recreated your new SQL string.

Note: I did not test this code. But, it should give you the idea how to do it.

NEWSQL = ""

Check Field1 (Text Type):
If Not IsNull(Me.Field1) then
If IsNull(NEWSQL) then
NEWSQL= "Field1 = '" & Me.Field1 & "'"
Else
NEWSQL= NEWSQL & " AND Field1 = '" & Me.Field1 & "'"
End If
Endif

Check Field2 (Number Type):
If Not IsNull(Me.Field2) then
If IsNull(NEWSQL) then
NEWSQL= "Field2 = '" & Me.Field2 & "'"
Else
NEWSQL= NEWSQL & " AND Field2 = '" & Me.Field2 & "'"
End If
Endif

Check Field4, Field5, ...., etc:

Me.NewList.RowSource = "SELECT * FROM SOMETABLE WHERE " & NewSQL

Hope this will get you started.

[June7]

So the main form is unbound, just has the unbound controls for filter input? This structure could be simplified to a single form in Continuous view with the filter controls in form header. Controls in the detail section can be arranged to look like Datasheet. The form RecordSource could be a parameterized SQL statement that references the filter controls as inputs using the LIKE and wildcard. Then the only code needed is to requery the form.

http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...earchform.html

However, as ItsMe pointed out, the LIKE/wildcard technique requires a value in each record. If Null is possible that will have to be handled by creating fields in query with expression that converts Null to a string and then apply the filter criteria to those constructed fields.

[ItsMe]

However, as ItsMe pointed out, the LIKE/wildcard technique requires a value in each record. If Null is possible that will have to be handled by creating fields in query with expression that converts Null to a string and then apply the filter criteria to those constructed fields.

The code in post 2 will handle fields that are Null. You just need to start with a field that has some data. I grabbed this example from a form where there is a combobox or a listbox to get things started. I believe I use similar code where the user has an option to select any field. They can search with one criteria or place values in all.

[user9]

I use the following code or something similar to create search forms. You can use wildcards to allow for beginig of field, middle of field search, etc. This example assumes there will always be a value in Field1.

If Not IsNull(Me.Field1) Then
    strWhere = "[WorkOrder] LIKE '" & Me.Field1 & "'" ' a wild card is not used here. field must match exactly
Else
Exit Sub
End If
If Not IsNull(Me.Field2) Then
    strWhere = (strWhere & " AND ") & "[Container] LIKE '" & Me.Field2 & "*'"
End If
If Not IsNull(Me.Field3) Then
    strWhere = (strWhere & " AND ") & "[PO] LIKE '" & Me.Field3 & "*'"
End If

Thanks for the responses. Using some of the suggestions in this thread, I created the following:

   
    Dim varName     As Variant
    Dim varCity     As Variant
    Dim strFilter  As String
    
    varName = Me.Name_Search
    varCity = Me.City_Search
    
    If varName <> "" And _
        IsNull(varCity) Then
        strFilter = "(([LAST_NAME_PA] = '" & varName & "' " _
            & "OR [LAST_NAME_PO] = '" & varName & "' " _
            & "OR [FIRST_NAME_PO] = '" & varName & "' " _
            & "OR [BUSINESS_PO] = '" & varName & "') "
    End If
    
    If Not IsNull(strFilter) And Not IsNull(varCity) Then
        MsgBox "Name and City Populated"
        strFilter = (strFilter & "AND ") _
            & "[CITY_PA] LIKE '" & varCity & "'" _
            & "OR [CITY_PC] LIKE '" & varCity & "'" _
            & "OR [CITY_PO] LIKE '" & varCity & "'"
    End If

Unfortunately, it's returning wherever there is a record with EITHER the Name or the City... not only records that have both. Can you see where my syntax error is here?

[June7]

The code uses OR operator.

[user9]

The code uses OR operator.

If there is a name/city in multiple different fields I need to return those records. Where is the OR error?

[June7]

Why are you using Variant type instead of String?

strName = Nz(Me.Name_Search,"")
strCity = Nz(Me.City_Search,"")

If strName <> "" And strCity = "" Then
...
End If

If strFilter<> "" And strCity <> "" Then
...
End If

I think you need parens around the city OR string, just like the name OR string is enclosed in a pair.

[user9]

Why are you using Variant type instead of String?

strName = Nz(Me.Name_Search,"")
strCity = Nz(Me.City_Search,"")

If strName <> "" And strCity = "" Then
...
End If

If strFilter<> "" And strCity <> "" Then
...
End If

I think you need parens around the city OR string, just like the name OR string is enclosed in a pair.

Thank you June! I will work with this direction.

I'm using variants given the potential for the fields to be null.

[user9]

I think I got what I need here:

(set filter)

If strName <> "" And _
strCity = "" And strPhone = "" And strPropID = "" Then
strFilter = strFilter
End If

If strFilter <> "" And _
strCity <> "" Then
strFilter = (strFilter) & "And " _
& "(([CITY_PC] LIKE '" & strCity & "'" _
& " OR [CITY_PC] LIKE '" & strCity & "'" _
& " OR [CITY_PO] LIKE '" & strCity & "'))"
Me.Filter = strFilter
Me.FilterOn = True
Me.Requery
End If

Now I just need to apply this to two additional search fields. Thanks for your assistance!

[ItsMe]

You need one more space to seperate your AND operator.

Try
strFilter = (strFilter & " AND ")