Filter as you type in multiple fields

[kallu]

He there, In access form, I have below code, to filter while typing. Its working well, but if any filed is empty or null, not showing results. example: FirstName is not null, LastName is null, there is no results.

Can somebady help me to solve this? I am new in VBA, be specific in giving sugestion.

Code:

Private Sub FilterFirstName_Change()
Dim FName
FName = FilterFirstName.Text
If Trim(FName & "") = vbNullString And IsNull(FilterLastName) And IsNull(FilterPhones) Then
Me.Filter = ""
Me.FilterOn = False


Me.FilterFirstName.SetFocus
Else
Me.Filter = "FirstName Like '*" & FilterFirstName.Text & "*'" & _ " And LastName Like '*" & FilterLastName & "*'" & _ "
And Phones Like '*" & FilterPhones & "*'"
Me.FilterOn = True
Me.FilterFirstName.SelStart = FilterFirstName.SelLength
End If
End Sub

[Minty]

I would recommend looking at Majp's sample database's here:
https://www.access-programmers.co.uk...x-form.327863/

He has created classes for these things that are completely portable.

[moke123]

Here's the basics . . .

Option Compare Database
Option Explicit

Private blnSpace As Boolean

Private Sub Text3_Change()

    Dim strFilter As String

    If blnSpace = False Then
    
        strFilter = "FirstName Like ""*" & Me.Text3.Text & "*"" or LastName like ""*" & Me.Text3.Text & "*"""

        Me.Text5 = strFilter   ' this line is just for the demo to display the filter string on the form

        Me.Filter = strFilter
        Me.Requery
        Refresh
        Me.Text3.SetFocus
        Me.Text3.SelStart = Len(Me.Text3.Text)
        
    End If
    
    Me.FilterOn = True
       
End Sub
 
Private Sub Text3_KeyPress(KeyAscii As Integer)

    If KeyAscii = 32 Then
        blnSpace = True
    Else
        blnSpace = False
    End If
    
End Sub

Here's a sample DB

[kallu]

Thanks Moke123

Working well to filter in one text box. I want to filter as typing from multiple search fields. example: to filter FirstName, than LastName, Phones etc. Find attached sample DB.

FilterAsYouTypeFromMultipleSearchTextAndFileds.zip

[Welshgasman]

I think you are going to need a button for that, or something to indicate when to search.

Otherwise you will search for Kallu Davies, then change the kallu to dimitri, but there is no dimitri davies?

So enter your criteria, then hit a button to search.
Test each control to see if a value is there. If so, build the sql string accordingly.

[kallu]

Yes, I have code to filter by button. The reason I like the search while typing is that the search is more accurate and less time consuming. For example If you are searching by name and there is no result, search by last name and if there is a result, you can compare with the first name (there are cases when the name is registered incorrectly), to verify if it is the person you were looking for.

[Welshgasman]

Search will not be more accurate?
How can it?.
Might be marginally quicker though.

[kallu]

If the name is entered incorrectly, and you search by first and last name, it will not give a result. Usually users search by first and last name or more fields, and the chance of a result is less due to misspelled entries.

[CJ_London]

Personally I would remove the first * from your search string so indexes can be used. Will be much faster for larger datasets. If on the very rare occasions where your users don’t know the first letter of the name, train them to use the *

[moke123]

If the name is entered incorrectly, and you search by first and last name, it will not give a result.

There are ways around spelling errors. There are things like Soundex and Levenschtein Distance that will calculate near matches.

[orange]

If the name is entered incorrectly, and you search by first and last name, it will not give a result.

Shouldn't you program for "No record found" or "Name not found"? This would be a result.

Have you done any analysis on what search term(s) the user used that gave "no result"? Seems like a key step in providing some corrective action(s) or revised training.

You could create a small routine to record what the users' search terms were and if no result -record the term(s) for review. If the search was successful, then don't save the term(s).

[kallu]

I solved this thread, by populating all empty fields with "NULL", and for new records, I put default value to "NULL", to fields which I need to search.
Thanks to everybody!

[Welshgasman]

"Null" is a string?
Null is Null.