[Gambas-user] DataSource filter using Like operator

[T Lee Davidson]

I'm fairly sure you need to use wildcards to represent the part of the string not searched for. Try:
$findtext = "*" & TextBox1.Text & "*"


-- 
Lee


On 05/04/2018 02:04 PM, Ernest Wilson wrote:
> 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