Subject Re: SQL statement not working on the form From AGOSTINHO <AGOSTINHOTEIXEIRA@YAHOO.COM> Date Sun, 30 Aug 2020 23:19:10 -0400 Newsgroups dbase.getting-started Dear Mervyn Bick I appreciate your assistance to this issue. I'm still very confused and don't see how to put this form work. I don't know if it to much asking if you can make this form work from the top to the bottom, so I can just copy the whole thing and paste it to the form and make it work. My database is a products.dbf I want to  add a GRID to the form that have to list the itemcode,brand,description fields An entryfield that will input the search criteria lets say ordered by brand name. Yes very interesting where you say that this can work showing the result on the GRID as you type into the entryfield. Once again thank you very much and be safe Agostinho Mervyn Bick Wrote: > 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.