Subject Re: SQL statement not working on the form From Mervyn Bick <invalid@invalid.invalid> Date Sun, 30 Aug 2020 15:04:25 +0200 Newsgroups dbase.getting-started On 2020-08-30 05:02, AGOSTINHO wrote: > Dear Group can anyone help me with the following? > As you can see on the form bellow, I'm trying to do a kind of filter on my database on onlostFocus() using a SQL command line but it does not return any filtering on the GRID. > When using this method direct on the command window(DOT PROMPT) > > use products > use products.dbf USE is the dBASE command to open a table in a work area. These two lines do exactly the same thing if you are using .dbf files. You only need one of them.  dBASE also works very well with Paradox files which have the .db extension.  If you are trying to access a paradox file then you need to specify the extension e.g. use products.db > ag="AGU" > SELECT * FROM products WHERE merk LIKE '%&ag%' > browse When used like this SELECT (which is a localSQL command and not a dBASE command) also opens the table in a workarea.  The first two lines are, therefore, redundant. The dBASE & macro operator is not normally recognised by SQL and, although it works here, you should be using a parameter with the SELECT command.  To indicate that it is a parameter one uses a : in front of the variable name ag = '%AGU%' select * from products where merk like :ag browse > Than  I get the filtering result by browsing the database file > Please let me know what I'm doing wrong within the form that I can't get the > result as from the command windows. > Thanks Using an entryfield's onLostFocus event handler to filter a rowset in a form can work but it's certainly not a particularly good idea. In your form you display the entire contents of your table in the grid when you open the form.  When the entryfield loses focus a workarea is created in memory containing the relevant records (if any) but there is nothing on the form to display the records. A better idea (although there are other options as well) is to use the entryfield's onKey event handler in conjunction with a parameter driven query to filter the rowset displayed in the grid. It's never a good idea to hardcode a file's path in a query. Rather use a User BDE Alias in conjunction with a database object.  If you completed the tutorial you should already have the User BDE Alias set up.     this.DBASETUTORIAL1 = new DATABASE(this)     with (this.DBASETUTORIAL1)        left = 22.0        top = 2.0        width = 11.0        height = 1.0        databaseName = "DBASETUTORIAL"        active = true     endwith     this.PRODUCTS1 = new QUERY(this)     with (this.PRODUCTS1)        left = 14.0        top = 12.0        database = form.dbasetutorial1        sql = 'select * from "PRODUCTS.DBF" where merk like :ag'        params['ag'] = '%' //this will open with all records displayed        active = true     endwith     function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl)        form.products1.params['ag'] = '%'+this.value+'%'        form.products1.requery()        return As you type each character into entryfield1 fewer an fewer records will be displayed in the grid. To make the filter not case sensitive use the following in the query sql = 'select * from "PRODUCTS.DBF" where lower(merk) like lower(:ag)' Mervyn.