Search Multiple Data Using Search Button in a Form

[spideynok]

Hi! I had a code that can search a data just using keyword like 123 it will show me the record that has 123 like "31231", "32123", etc. but I want to search multiple record which can be separated by comma like [123, 456] and the result will show me the record that is..



31231
32123
32456
24561

LEGEND
• GOLDNEWCONNECT = Database/Table
• GOLDNEWCONNECT_ValidateSubform = Subform on my main form
• GOLDNEWCONNECT_ValidateBatch = The name of the main form
• Text30 = Textbox where SR Number will be putted
• Text34 = Textbox where Emboss Name will be putted

I had a code like this.

This will search the SR Number, which I change the code in the BuildFilter because I want to search multiple record which is separated by Comma which has an error.

BUTTON CODE FOR SR NUMBER

Private Sub Command33_Click()
 'Update the record source
    Me.GOLDNEWCONNECT_ValidateSubform.Form.RecordSource = "SELECT * FROM GOLDNEWCONNECT " & BuildFilter
        
    ' Requery the subform
    Me.GOLDNEWCONNECT_ValidateSubform.Requery
End Sub

and this will search for the Emboss Name which I didn't change the code. For you also to see the search Like code, It's running but it doesn't search in multiple, just one at a time.

BUTTON CODE FOR EMBOSS NAME

Private Sub Command36_Click()
'Update the record source
    Me.GOLDNEWCONNECT_ValidateSubform.Form.RecordSource = "SELECT * FROM GOLDNEWCONNECT " & BuildFilter
        
    ' Requery the subform
    Me.GOLDNEWCONNECT_ValidateSubform.Requery
End Sub

and this is the BuildFilter code.

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    
    varWhere = Null  ' Main filter
 
    ' Check for LIKE First Name
    If Me.Text30 > "" Then
        varWhere = varWhere & "[SR_Number] LIKE In('*" & Me.Text30 & "*')"
    End If
        
   If Me.Text34 > "" Then
        varWhere = varWhere & "[Emboss_Name] LIKE ""*" & Me.Text34 & "*"" AND "
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
    
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

as you can see. I just put In() on the code because I read that I can you IN() so I can search using/separating comma.

I don't know if I'm doing this right but please help me. This one will be pass tomorrow

Thank you in advanced..

[June7]

The IN operator acting on the string from textbox won't work because it is a single string that contains comma characters, not multiple strings separated by commas. Example:

This query works:
SELECT FAAID FROM Airports WHERE FAAID IN("ANC", "FAI");

This query does not work:
SELECT FAAID FROM Airports WHERE FAAID IN("ANC,FAI");

Throw the LIKE operator in and query errors. Each operator requires its own criteria.

[spideynok]

But what if I don't use query? The subform is just getting some fields on the table and not getting a query. I'm super confused if I did this right or wrong? or what is wrong with it?

Is the error is in this code?

 varWhere = varWhere & "[SR_Number] LIKE In('*" & Me.Text30 & "*')"

[June7]

The function is building a string for the WHERE clause and passing that string to the calling procedure which then completes the SQL statement. Doesn't matter if the SQL is RecordSource, RowSource, Access query object, VBA recordset - the syntax rules are the same. As explained that syntax will not work.

Textbox entry of <123, 456> is one string of characters, one piece of data, not a set of two distinct values. The IN operator requires a set of distinct values. Your textbox produces a set of 1 element.

[spideynok]

But I have this code

 With Me
    If .txtFindSRNumber > "" Then
        strFilter = & Replace(txtFindSRNumber, ", ", ", ") 
        strFilter = "[SR_Number] In ('*" & Replace(strFilter, ", ", "','") & "*')"
    End If
.Filter = strFilter
    .FilterOn = (strFilter > "")
    End With

when I input a value it's okay to put <123, 456> but the thing is you need to be specific. because it will post only "123" and "456" not 1234. 4567.

[June7]

I see, the code incorporates the ' delimiters to define the distinct set elements. Yes, something I was mulling over and just tested. It worked. However, the LIKE and wildcards will not work in conjuction with the IN and its set.

[spideynok]

oh I see. That's why even I play on the code around that one. It shows me error always hehe! Thanks.

But what kind of coding can I use to perform that Like search that can be separated by comma?

[June7]

I envision a more complex function that would parse the string then build a long statement with multiple LIKE and wildcard operators. So you end up with:

[SR_Number] LIKE '*123*' OR [SR_Number] LIKE '*456*' OR ...

[spideynok]

uhmm is that the same when I just want to search the value inputted on Text30. and not putting '*123*' ?

[June7]

Don't understand the question. How would that be the same?

[spideynok]

[SR_Number] LIKE '*123*' OR [SR_Number] LIKE '*456*' OR ...

^ This one is like you're searching for 123 and 456..
what if I change the 123 and 456 to.. Me.Text30..

Text30 is the name of the textbox and I want to get the value on that textbox. Meaning the LIKE will depend on the value putted on Text30.

[June7]

Oh, my example was to show the result of a function processing input to textbox.

[spideynok]

hehe yes. the result on this will also seen on the subform