Spacing Issue with Dynamic Search

[steve21nj]

I was wondering if anyone has worked with John Big Booty's dynamic search.

The search itself is great but was wondering how can you show the results if a user misses a space. For example if I am searching for A 201 999 888 but I enter A201999888, it will not display results. Actually if I enter A2, the list goes blank. I'd like to be able to search A*2*0*1*9*9*9*8*8*8* if possible.

Does that create additional problems? Any suggestions?

http://www.access-programmers.co.uk/forums/showthread.php?t=188663

Private Sub SearchFor_Change()
'Create a string (text) variable
    Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString
'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery
 
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
 
        Exit Sub
    End If
'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If
End Sub

 

[MStef]

Look at "DemoFindLikeA2003.mdb" (attachment, zip).
Open Form1 and try. Adapt it in your mdb.

 

[steve21nj]

Thank you for the reply but this only focuses on the first column’s information and doesn’t handle spacing.

For example, in your table, if you were to change the first record to [A 100333 ],(a space after the A), and the second record to [A101999888], it wouldn’t find both records by entering A1 or A 1. It would only find specific to the search. I’d like to have the results find both.

 

[MStef]

Yes, you have to be careful, If you write "A 100333" then you have to write on same way always. Try to use INPUT MASK properties.

 

[steve21nj]

In my search input, you can enter a date, name, or file number. I would not be able to enter an input mask. For my example, I was specifically focusing on the file number. Hope that helps.