[SOLVED] How to change this Access SQL to select similar rather than identical records

How to change this Access SQL to select similar rather than identical records

The following code works, as I type data the Dropdown is populated with fewer and fewer records until either only one remains, or I click to select a record from a small number of records. However sometimes I click the wrong name e.g. John Jones rather than John Smith and then the Comment_Enter() code just populates the Dropdown with John Jones; how can I change the code so the LIKE selection ignores the last few (or x) characters in the record string, to populate the Dropdown with a few records surrounding the one I really want to select?

Private Sub Comment_Change()

Dim strRowSource As String

   If Len(Me!Comment.Text) > 2 Then 'when more than 2 characters are entered the following code runs
        strRowSource = "SELECT DISTINCT [Purchase ledger].Comment FROM [Purchase ledger]"
        
        strRowSource = strRowSource & " WHERE Comment Like '"
        strRowSource = strRowSource & Me!Comment.Text
        strRowSource = strRowSource & "*'"
        
        Me!Comment.RowSource = strRowSource
        Me!Comment.Dropdown
   End If
End Sub

Private Sub Comment_Enter()

Dim strRowSource As String

    If Len(Me!Comment.Text) > 2 Then 'when more than 2 characters are entered the following code runs
        strRowSource = "SELECT DISTINCT [Purchase ledger].Comment FROM [Purchase ledger]"

        strRowSource = strRowSource & " WHERE Comment Like '"
        strRowSource = strRowSource & Me!Comment.Text
        strRowSource = strRowSource & "*'"
        
        Me!Comment.RowSource = strRowSource
        Me!Comment.Dropdown
    End If
End Sub

Nothing as I do not understand exactly how the LIKE selection works; I adapted it from elsewhere.

Solution

To give a strict answer to your question, this example. If the length of the filter string is longer than 6 characters, this code will add results to the search that match the given string minus the 3 last characters:

   strRowSource = strRowSource & " WHERE Comment Like '"
   strRowSource = strRowSource & Me!Comment.Text
   strRowSource = strRowSource & "*' "
   if(not isnull(Me!Comment.Text) and len(Me!Comment.Text) > 6) then        
    strRowSource = strRowSource & " OR Comment Like '" & left(Me!Comment.Text, len(Me!Comment.Text)-3)
    strRowSource = strRowSource & "*' "
   end if