Subject Re: Like query From Mervyn Bick <invalid@invalid.invalid> Date Wed, 25 May 2022 11:45:30 +0200 Newsgroups dbase.getting-started Attachment(s) like_search1.wfm On 2022/05/24 20:23, Tom wrote: > dBase 13.1 > Windows 10 Pro > Version 21H2 > > Having stolen one of Mervyn's sample forms, I have changed databases, > tables and fields for a trial. The field name contains a space but > otherwise is a character field. Using single quotes, double quotes and > brackets around the field name still fails to 'find' any entries I type. > Ultimately, I would like to use 'like' with the date field in the table > too. > > Would you be so kind as to show me how to get this form to work. A copy > of the sample form and a screenshot of the table design are included. I'm afraid the problem is due to the limitations of localSQL.  In "proper" SQL, such as Firebird, [select * from invoice where lower("CARD NAME") like :search] works as expected. Note double quotes, not single quotes. Outside of a SELECT statement localSQL can deal with spaces in fieldnames by wrapping the fieldname in single quotes and using the table name (or a correlation name i.e alias) e.g invoice.'card name' The LIKE predicate only works with character fields.  To get it to work with a date you need to convert the field to characters. The cast() function in SQL will do this but once again localSQL has a problem with the space in a fieldname in a SELECT statement. The solution is to add two new columns temporarily, populate them and then remove the two fields when the form closes. The two fields are not visible when the form opens but you can scroll across in the grid to view them. A revised version of your form is attached. Mervyn. try    open database dbasetutorial catch(exception e) endtry alter table :dbasetutorial:invoice add cName char(40) ,add cOrder char(10) update :dbasetutorial:invoice set cName = invoice.'card name', cOrder = cast(invoice.'order date' as char(10)) //Cast will present the date as characters 'mm/dd/yyyy' irrespective of date format. //The lines below will present the date as characters in the same format as the date. //I use yyyy/mm/dd as the date format at so I would actually prefer to use dtos() instead of dtoc() for //the search field as this presents the character date as yyyymmdd without separators. //Uncomment the lines below if you want a different format in the search field. This will overwite //the existing mm/dd/yyyy values inserted by the update command. //use :dbasetutorial:invoice //replace all cOrder with dtoc(:order date:) // //note how to deal with spaces in fieldnames when using XDML //use ** END HEADER -- do not remove this line // // Generated on 2022-05-25 // parameter bModal local f f = new like_search1Form() if (bModal)    f.mdi = false // ensure not MDI    f.readModal() else    f.open() endif class like_search1Form of FORM    with (this)       onOpen = class::FORM_ONOPEN       onClose = class::FORM_ONCLOSE       height = 17.5909       left = 1.4286       top = 0.4091       width = 148.2857       text = ""    endwith    this.DBASETUTORIAL1 = new DATABASE(this)    with (this.DBASETUTORIAL1)       left = 12.0       width = 11.0       height = 1.0       databaseName = "DBASETUTORIAL"       active = true    endwith    this.INVOICE1 = new QUERY(this)    with (this.INVOICE1)       left = 26.0       width = 6.0       height = 1.0       database = form.dbasetutorial1       sql = "select * from INVOICE.DBF where lower(cName) like :search and cOrder like :dsearch "       params["search"] = "%"       params["dsearch"] = "%"    endwith    this.GRID1 = new GRID(this)    with (this.GRID1)       dataLink = form.invoice1.rowset       height = 8.6364       left = 5.1429       top = 3.0909       width = 139.8571    endwith    this.RADIOBUTTON1 = new RADIOBUTTON(this)    with (this.RADIOBUTTON1)       height = 1.0909       left = 11.4286       top = 13.0       width = 15.7143       text = "Contains"       group = true       value = true    endwith    this.RADIOBUTTON2 = new RADIOBUTTON(this)    with (this.RADIOBUTTON2)       height = 1.0909       left = 10.7143       top = 15.4091       width = 15.7143       text = "Begins with"    endwith    this.ENTRYFIELD1 = new ENTRYFIELD(this)    with (this.ENTRYFIELD1)       onGotFocus = class::ENTRYFIELD1_ONGOTFOCUS       onKey = class::ENTRYFIELD1_ONKEY       height = 1.0       left = 38.5714       top = 14.1818       width = 17.4286       value = " "    endwith    this.TEXTLABEL1 = new TEXTLABEL(this)    with (this.TEXTLABEL1)       height = 1.0       left = 35.5714       top = 13.0       width = 23.5714       text = "Search for (case insensitive)"    endwith    this.ENTRYFIELD2 = new ENTRYFIELD(this)    with (this.ENTRYFIELD2)       onGotFocus = class::ENTRYFIELD2_ONGOTFOCUS       onKey = class::ENTRYFIELD2_ONKEY       height = 1.0       left = 69.0       top = 14.1818       width = 19.0       value = ""    endwith    this.TEXTLABEL2 = new TEXTLABEL(this)    with (this.TEXTLABEL2)       height = 1.0       left = 69.0       top = 13.0       width = 21.0       text = "Search for Order Date"    endwith    function ENTRYFIELD1_onGotFocus()       form.entryfield2.value = ''       return    function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl)       ***if nChar = 13  //uncomment to prevent requery until Enter is pressed          if form.radiobutton1.value = true             form.INVOICE1.params['search'] = '%'+trim(lower(form.entryfield1.value))+'%'                                 form.invoice1.params['dsearch'] = '%'          else             form.INVOICE1.params['search'] = trim(lower(form.entryfield1.value))+'%'                                 form.invoice1.params['dsearch']  = '%'          endif          if empty(form.entryfield1.value)             form.INVOICE1.params['search'] = '%'          endif                          if empty(form.entryfield2.value)             form.INVOICE1.params['dsearch'] = '%'          endif          form.INVOICE1.requery()       ***endif       return    function ENTRYFIELD2_onGotFocus()       form.entryfield1.value = ''       return    function ENTRYFIELD2_onKey(nChar, nPosition,bShift,bControl)       ***if nChar = 13  //uncomment to prevent requery until Enter is pressed          if form.radiobutton1.value = true             form.INVOICE1.params['dsearch'] = '%'+trim(form.entryfield2.value)+'%'                                 form.invoice1.params['search'] = '%'          else             form.INVOICE1.params['dsearch'] = trim(form.entryfield1.value)+'%'                                 form.invoice1.params['search']  = '%'          endif          if empty(form.entryfield1.value)             form.INVOICE1.params['search'] = '%'          endif                          if empty(form.entryfield2.value)             form.INVOICE1.params['dsearch'] = '%'          endif          form.INVOICE1.requery()       ***endif             return    function form_onClose()       form.invoice1.active = false       cmd = 'alter table invoice drop cName, drop cOrder'       form.dbasetutorial1.executeSQL(cmd)       return    function form_onOpen()             form.invoice1.active = true  // making the query active here prevents error if query is active when form opens        form.entryfield1.setfocus()       return endclass