[Gambas-user] DataSource filter using Like operator

[Ernest Wilson]

I am using Gambas 3.10.0 and trying to implement a "Find" on a form 
displaying a database of people. So far, the form works perfectly for 
maintaining the database, however, the "Find" is not working as I would 
like. I have attempted to implement the "Find" using a DataSource filter 
as described below, but, the result is not as I expected. Any guidance 
will be appreciated.

On a form I have a DataBrowser displaying the content of an SQLite 
database table containing a list of people. The fields consist of:

     ID - ID Number
     FName - Persons first name and possibly an initial
     LName - Persons surname
     Company - Persons employer
     Various other fields with persons information

Also, on the form are a TextBox for user entry, a button for "Find" and 
a button for "Clear".

The desired action is as follows:

User enters any substring of the persons name (first name or surname) in 
the TextBox and clicks the "Find" Button. The matching records are 
displayed in the DataBrowser. When the user clicks the "Clear" button, 
the DataBrowser displays all the records.

Example: If I have records for (1) Jack Smith, (2) Sally Simple, (3) 
Jack B. Nimble, (4) John Dough, (5) Jack B. Quick, entering:

"Jack" should return records 1, 3, 5
"im" should return records 2, 3
"s" should return records 1, 2
"Jack B" should return records 3, 5

I have implemented the "Find" button as follows:

Public Sub Button1_Click()
   'Find Button
   Dim $findtext As String
   Dim $filterstr As String

   $findtext = TextBox1.Text 'Assignment to a variable seems to be 
required for DB.Subst

   $filterstr = DB.Subst("LName LIKE &1 OR FName LIKE &1", $findtext) 
'Look for Find entry in either First or Last name

   TextBox2.Text = $filterstr 'Debugging output

   DataSource1.Filter = $filterstr
   DataBrowser1.Refresh
   FormatDataBrowser1 'Set column widths, header alignment, etc.
End

The result I get is only an exact match of the string entered in the 
TextBox, as if I had used "=" instead of "Like" as the operator in the 
filter string.

I am obviously missing something, or have taken a wrong approach. 
Suggestions will be appreciated.

Regards,

Ernest Wilson