Better search and filter VBA

[Dazza666]

Hi,

I'm trying to create a txt box that allows searching a subform of client records by either firstname surname or national insurance number, so the user could type the first name, surname and national insurance number or any combination of them and it would filter the sub form appropriately. I succeeded to a degree with the below code but it's very crude code and users must specify the in a specific order, first name, surname, national insurance number (or any combination in that order) I wondered if anyone could provide a better solution or point me in the right direction?



Private Sub btnSearch_Click()
If IsNull(Me.txtSearch) Then
Else

Dim strTxtArray() As String
strTxtArray() = Split(Me.txtSearch)



Dim strtxt1 As String
strtxt1 = strTxtArray(0)

If UBound(strTxtArray) = 1 Then
Dim strtxt2 As String
strtxt2 = strTxtArray(1)
Else
End If
If UBound(strTxtArray) = 2 Then
Dim strtxt3 As String
strtxt3 = strTxtArray(2)

Else
End If

Select Case UBound(strTxtArray)
Case 0

Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+[Forms]![InitialReferral]![txtSearch]+'*') OR (Client.[Surname]) like '*' + [FORMS]![InitialReferral]![TxtSearch] + '*' OR (Client.[NationalInsuranceNumber]) like '*' + [FORMS]![InitialReferral]![TxtSearch] + '*' )"
Case 1

Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+" & "'" & strtxt1 & "'" & "+ '*') and (Client.[Surname]) like '*' +" & "'" & strtxt2 & "'" & " + '*') "
Case 2

Me.frmClientDatasheet.Form.RecordSource = "SELECT Client.[First Name], Client.Surname, Client.NationalInsuranceNumber, Client.DateOfBirth, Client.ID FROM Client WHERE (((Client.[First Name]) Like '*'+" & "'" & strtxt1 & "'" & "+ '*') and (Client.[Surname]) like '*' +" & "'" & strtxt2 & "'" & " + '*' And (Client.[NationalInsuranceNumber]) like '*' +" & "'" & strtxt3 & "'" & " + '*' )"
End Select

End If

End Sub

[pbaldy]

I would probably have a separate textbox for each possible input and build the SQL dynamically. The sample db here demonstrates this:

http://www.baldyweb.com/BuildSQL.htm

[Dazza666]

I would probably have a separate textbox for each possible input and build the SQL dynamically. The sample db here demonstrates this:

http://www.baldyweb.com/BuildSQL.htm

Hey,

thanks for your response,

I was trying to avoid multiple text boxes, I deal with a lot of people who do not really understand computers at all and if possible i'd like to keep the application as simple as possible (even if it drives up development time) I appreciate there may be a training issue there as well but i'm curious to see if it can be done?

[pbaldy]

To me it's simpler (or more intuitive for the user) to have multiple textboxes, each with a clear purpose, than a single textbox that requires a specific format and order of entry. That said, it's your program, so you're free to design it any way you believe is best for your users.

[Dazza666]

Yeah I mean part of the point was I don't want them to have to enter it in any particular order that's kind of the basis for my question, to help improve upon what I have already done. but thanks anyway

[pbaldy]

If you had 3 different types of data you might be able to, but programmatically how can you tell if they've entered "firstname, surname" or "surname, firstname"? I don't think you can, because it's all text.

[Dazza666]

If you had 3 different types of data you might be able to, but programmatically how can you tell if they've entered "firstname, surname" or "surname, firstname"? I don't think you can, because it's all text.

Unless there is a better way to write the SQL string? Is it possible to run a "like" clause across multiple fields? ... WHERE client.firstname or client.surname or client.NationalInsuranceNumber LIKE strtxt1 or strtxt2 or strtxt3? Something like that, I obviously don't mind if the DB checks for 'Dave' in the NationalInsuranceNumber field so really i'm trying to ask it to check all words against all fields.

[pbaldy]

The syntax would be more like this pseudo-SQL:

WHERE FirstName Like str1 OR FirstName Like str2 OR FirstName Like str3 OR LastName Like str1 OR LastName Like str2 OR LastName Like str3