Split() function and searching with the results

[NotAnExpert]

Hi all, I am trying to gather a bit more knowledge and am working towards a search form which will help me through my day a bit more efficiently.

I have a mainform, and a subform.

The main form is unbound and has several textboxes which when not null or empty, are used as query criteria to filter the information from one large text field called 'description' on the main table to the subform. Now this works well, but I've seen this Split() function which I imagine will be more useful in future.

My example is as follows:

Private Sub txtSearch_AfterUpdate()
    Dim searchArray() As String
      
    searchArray = Split(Me.txtSearch.Text, ";")
    
    Dim i As Integer
    
    For i = LBound(searchArray) To UBound(searchArray)
        MsgBox "Search Text: " & searchArray(i), vbOKOnly, "Your Search"
    Next
End Sub

At the moment the test simply hands message boxes with the split data previously entered into the search textbox. Yaye, the split function is collecting data from the right place and giving it back to me.

What I was wanting this to do is us the searchArray(i) result as the query criteria...

I'm just not sure how to formulate it.

Any pointers would be great, thank you.

 

[theDBguy]

Hi. Check out the Join() function. Hope that helps...

 

[NotAnExpert]

Hi. Check out the Join() function. Hope that helps...

Thank you for coming back so quickly. My apologies, it seems the join function might not be what i'm looking for.

I want to reduce the number of controls on the form from 6 textboxes down to 1, but I still want the same functionality of being able to search for multiple LIKE's within one field on the table in any given order. The reason being, the Description field on the table which this will be used on is not uniformly put together, so things like colour, size, standard will be searchable with the use of 'like "*" & nz(textbox1, "*") & "*"...

Ideally I want the code to replace the textbox1 with searchArray(i) if there is one?

I apologise, my knowledge on this is limited so i'm trying to explain it the best way I can...

 

[MajP]

Some kind of modification of this

Public Sub TestControlFilters()
  Debug.Print FindSplit("MyField", "Some;Delimited;Field")
End Sub

Public Function FindSplit(FieldName As String, DelimitedSearchString As String, Optional Delimiter As String = ";") As String
  Dim aStr() As String
  Dim i As Integer
  aStr = Split(DelimitedSearchString, Delimiter)
  For i = 0 To UBound(aStr)
    If FindSplit = "" Then
      FindSplit = FieldName & " LiKE '*" & aStr(i) & "*'"
    Else
      FindSplit = FindSplit & " AND " & FieldName & " Like '*" & aStr(i) & "*'"
    End If
  Next i
End Function

Output
MyField LiKE '*Some*' AND MyField Like '*Delimited*' AND MyField Like '*Field*'

 

[NotAnExpert]

Some kind of modification of this

Public Sub TestControlFilters()
  Debug.Print FindSplit("MyField", "Some;Delimited;Field")
End Sub

Public Function FindSplit(FieldName As String, DelimitedSearchString As String, Optional Delimiter As String = ";") As String
  Dim aStr() As String
  Dim i As Integer
  aStr = Split(DelimitedSearchString, Delimiter)
  For i = 0 To UBound(aStr)
    If FindSplit = "" Then
      FindSplit = FieldName & " LiKE '*" & aStr(i) & "*'"
    Else
      FindSplit = FindSplit & " AND " & FieldName & " Like '*" & aStr(i) & "*'"
    End If
  Next i
End Function

Output
MyField LiKE '*Some*' AND MyField Like '*Delimited*' AND MyField Like '*Field*'

Hi, thank you for the push especially with how to look at the FOR loop.

It took me the better part of today just trying to figure a few things out and being honest, I wasn't quite sure what your code above was doing, so after some experimentation, this is what I came up with based on what you showed me:

Private Sub btnSearch_Click()
    Dim searchArray() As String
    Dim i As Integer
    
    Dim sqlSELECT As String, sqlFROM As String, sqlWHERE As String, sqlORDER As String, strSQL As String
    
    sqlSELECT = "SELECT DocumentNo, ItemDescription, PrintSequenceNumber, LineQuantity, ItemCode, UnitSellingPrice "
    sqlFROM = "FROM dbo_JobListItems "
    sqlORDER = "ORDER BY DocumentNo DESC"
    
    If IsNull(Me.txtSearch) Then
        sqlWHERE = "WHERE ItemDescription LIKE '*" & Me.txtSearch & "*'"
    Else
        searchArray() = Split(Me.txtSearch.Value, ";")
        For i = 0 To UBound(searchArray)
            
            If i = 0 Then
                sqlWHERE = "WHERE ItemDescription LIKE '*" & searchArray(i) & "*'"
            Else
                sqlWHERE = sqlWHERE & " AND ItemDescription LIKE '*" & searchArray(i) & "*'"
            End If
            
        Next i
    End If
 
    strSQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER
    
    Me.subSearchItems.Form.RecordSource = strSQL
    Me.subSearchItems.Form.Requery
    
End Sub

I struggled for a short time because if the search textbox for started as a null, or the box was cleared in order to show all records, an error popped up regarding nulls which I assume is an issue with the Split() function?

I also opted to check the current index of the for loop in order to choose which of the options I would add to the string...

I know my code can probably be massively shortened, and I would be all too pleased to see what could be done with it?

Kindest regards

 

[MajP]

I know my code can probably be massively shortened

Not really, it is pretty succinct.

The split function will fail on a null since it requires a string as the input.
You could have avoided the if check and done
searchArray() = Split(Me.txtSearch.Value & "", ";")
a null & empty string is an empty string
The result would be an empty array since there is nothing to split on. Then it would fall the the loop.

 

[NotAnExpert]

Not really, it is pretty succinct.

The split function will fail on a null since it requires a string as the input.
You could have avoided the if check and done
searchArray() = Split(Me.txtSearch.Value & "", ";")
a null & empty string is an empty string
The result would be an empty array since there is nothing to split on. Then it would fall the the loop.

Hi MajP, thank you for that pointer, yes, I can see why the split & "" would make a string and not a null, that's great! Thank you for your help!