Subject Re: Like query
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 27 May 2022 12:20:15 +0200
Newsgroups dbase.getting-started
Attachment(s) like_search3.wfm

On 2022/05/26 22:57, Tom wrote:

> As I read the enclosed Local SQL Help file excerpt, you should be able
> to 'cast' the 'Order Date' field *directly* to a string as opposed to
> creating a new field and without permanent damage to the original date
> values. Is this possible?
OOPS. :-( I have no idea where I got the idea but for years I've
believed that fieldnames containing spaces were a problem in the SELECT
statement in a query object.  When I created the example for you I
simply created the temporary fields without actually trying to use the
fieldnames with spaces.

I set out to create a little example to show you it doesn't work and, of
course, the damn thing worked.  What I assumed to be a bug isn't.

I have now reworked the example so as not to need the temporary fields.
  As there are no temporary fields the query can be made active when it
opens. I've also added the ability to search on either or both fields.

Both sets of radio buttons use a common onChange() event handler and
both entryfields now  use a common onKey event handler.

Mervyn.







** END HEADER -- do not remove this line
//
// Generated on 2022-05-27
//
parameter bModal
local f
f = new like_search3Form()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class like_search3Form 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 i.*  from invoice i where lower(i.'card name') like :search and cast(i.'order date' as char(10)) like :dsearch "
      params["search"] = "%"
      params["dsearch"] = "%"
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.invoice1.rowset
      allowEditing = false
      height = 8.6364
      left = 5.1429
      top = 3.0909
      width = 139.8571
   endwith

   this.RADIOBUTTON1 = new RADIOBUTTON(this)
   with (this.RADIOBUTTON1)
      onChange = class::RADIOBUTTON_ONCHANGE
      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::ENTRYFIELD_ONKEY
      height = 1.0
      left = 83.0
      top = 13.9545
      width = 17.4286
      value = " "
   endwith

   this.TEXTLABEL1 = new TEXTLABEL(this)
   with (this.TEXTLABEL1)
      height = 1.0
      left = 80.0
      top = 12.7727
      width = 23.5714
      text = "Search for (case insensitive)"
   endwith

   this.ENTRYFIELD2 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD2)
      onGotFocus = class::ENTRYFIELD2_ONGOTFOCUS
      onKey = class::ENTRYFIELD_ONKEY
      height = 1.0
      left = 113.4286
      top = 13.9545
      width = 19.0
      value = ""
   endwith

   this.TEXTLABEL2 = new TEXTLABEL(this)
   with (this.TEXTLABEL2)
      height = 1.0
      left = 113.4286
      top = 12.7727
      width = 21.0
      text = "Search for Order Date"
   endwith

   this.RADIOBUTTON3 = new RADIOBUTTON(this)
   with (this.RADIOBUTTON3)
      onChange = class::RADIOBUTTON_ONCHANGE
      height = 1.0909
      left = 36.5714
      top = 13.2273
      width = 15.7143
      text = "Either field"
      group = true
      value = true
   endwith

   this.RADIOBUTTON4 = new RADIOBUTTON(this)
   with (this.RADIOBUTTON4)
      height = 1.0909
      left = 36.4286
      top = 15.4091
      width = 15.7143
      text = "Both fields"
   endwith


   function ENTRYFIELD1_onGotFocus()
      if form.radiobutton3.value = true
         form.entryfield2.value = ''
      endif
      return

   function ENTRYFIELD_onKey(nChar, nPosition,bShift,bControl)
      ***if nChar = 13  //uncomment to prevent requery until Enter is pressed
         if form.radiobutton1.value = true
            if empty(form.entryfield1.value)
               form.INVOICE1.params['search'] = '%'
            else
               form.INVOICE1.params['search'] = '%'+trim(lower(form.entryfield1.value))+'%'
            endif
            if empty(form.entryfield2.value)
               form.invoice1.params['dsearch'] = '%'
            else
               form.invoice1.params['dsearch'] = '%'+trim(form.entryfield2.value)+'%'
            endif
         elseif form.radiobutton2.value = true
            if empty(form.entryfield1.value)
               form.INVOICE1.params['search'] = '%'
            else
               form.INVOICE1.params['search'] = trim(lower(form.entryfield1.value))+'%'
            endif
            if empty(form.entryfield2.value)
               form.invoice1.params['dsearch'] = '%'
            else
               form.invoice1.params['dsearch'] = trim(form.entryfield2.value)+'%'
            endif
         endif
         form.INVOICE1.requery()
      ***endif
      return

   function ENTRYFIELD2_onGotFocus()
      if form.radiobutton3.value = true
            form.entryfield1.value = ''
      endif
      return

   function RADIOBUTTON_onChange()
      form.entryfield1.value = ''
      form.entryfield2.value = ''
      form.invoice1.params['search']  = '%'
      form.invoice1.params['dsearch']  = '%'
      form.invoice1.requery()
      return

   function form_onClose()
      form.invoice1.active = false
      return

   function form_onOpen()
       form.entryfield1.setfocus()
      return

endclass