Subject Re: Issuue with Template (picture) for an entryfield From Mervyn Bick <invalid@invalid.invalid> Date Sat, 16 Sep 2023 18:13:47 +0200 Newsgroups dbase.getting-started Attachment(s) date_picture.wfm On 2023/09/16 10:20, Heinz Kesting wrote: > Hi all, > for a filter form I want to restrain a date field to certain patterns, > for example DD.MM.YYYY for a complete date, DD.MM. or YYYY for other needs. > So I set the entryfield's picture property accordingly ("99.99.9999" for > a complete date, "99.99." or "9999" respectively). That all works nicely. > > However, for the pattern MM.YYYY (picture "99.9999") dBase replaces the > dot for the decimal point with a comma the moment some data is entered. > By 'Trial and Error' I found that this only happens when the picture > contains just ONE decimal point, like "99.9999". If I use "99.9999." for > this, it works fine, but I don't find it correct to have a dot after the > year (the fieldS value would then hold a string like "09.2023." which I > dislike) > > Is there a way around this? Instead of using the entryfield's picture property, the attached example uses the entryfield's key event handler to ensure that a point is used as the separator between day, month and year values. The example filters the rowset on a date field.  If you need to filter on a character field holding dates or portions of dates the onKey event handler will require more work. Mervyn. /* The form designer reformats the contents of the query's sql property.  This copy for easy reference       l0 = "select  e.*, "       l0 += "extract(day from e.hiredate) as dd, "       l0 += "extract(month from e.hiredate) as mm, "       l0 += "extract(year from e.hiredate) as yy "       l0 += "from EMPLOYEES e  "       l0 += "where (extract(day from hiredate) between :dd1 and :dd2 ) "       l0 += "and (extract(month from hiredate) between :mm1 and :mm2 ) "       l0 += "and (extract(year from hiredate) between :yy1 and :yy2 ) "       l0 += "order by  yy,mm,dd  "       sql = l0          */ ** END HEADER -- do not remove this line // // Generated on 2023-09-16 // parameter bModal local f f = new date_pictureForm() if (bModal)    f.mdi = false // ensure not MDI    f.readModal() else    f.open() endif class date_pictureForm of FORM    with (this)       onOpen = class::FORM_ONOPEN       height = 19.5909       left = 19.8571       top = 1.7727       width = 93.7143       text = ""    endwith    this.DBASESAMPLES1 = new DATABASE(this)    with (this.DBASESAMPLES1)       left = 26.0       width = 11.0       height = 1.0       databaseName = "DBASESAMPLES"       active = true    endwith    this.EMPLOYEES1 = new QUERY(this)    with (this.EMPLOYEES1)       left = 3.0       width = 8.0       height = 1.0       database = form.dbasesamples1       l0 = "select  e.*, "       l0 += "extract(day from e.hiredate) as dd, "       l0 += "extract(month from e.hiredate) as mm, "       l0 += "extract(year from e.hiredate) as yy "       l0 += "from EMPLOYEES e  "       l0 += "where (extract(day from hiredate) between :dd1 and :dd2 ) "       l0 += "and (extract(month from hiredate) between :mm1 and :mm2 ) "       l0 += "and (extract(year from hiredate) between :yy1 and :yy2 ) "       l0 += "order by  yy,mm,dd  "       sql = l0         params["dd1"] = 0       params["dd2"] = 31       params["mm1"] = 0       params["mm2"] = 12       params["yy1"] = 0       params["yy2"] = 3000       active = true    endwith    this.GRID1 = new GRID(this)    with (this.GRID1)       dataLink = form.employees1.rowset       height = 6.2273       left = 5.5714       top = 1.0       width = 82.7143    endwith    this.ENTRYFIELD1 = new ENTRYFIELD(this)    with (this.ENTRYFIELD1)       key = class::ENTRYFIELD1_KEY       onKey = class::ENTRYFIELD1_ONKEY       height = 1.0       left = 25.0       top = 17.3182       width = 24.7143       value = " "    endwith    this.TEXT1 = new TEXT(this)    with (this.TEXT1)       height = 8.8636       left = 25.0       top = 7.8182       width = 29.7143       text = ""    endwith    function ENTRYFIELD1_key(nChar, nPosition,bShift,bControl)       local lRet       if (nChar >= 48 and nChar <= 57) or nChar = 46 or nChar = 13 or nChar = 8          lRet = true       else          lRet = false       endif               return lRet    function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl)       if nChar = 13          class::default_params()          if empty(this.value)             //Do nothing except requery()          elseif  not '.'$this.value //year only             form.employees1.params['yy1'] = val(this.value)                     form.employees1.params['yy2'] = val(this.value)          elseif at('.',this.value,2) > 0 // 2 points i.e full date             form.employees1.params["dd1"] = val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["dd2"] = val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["mm1"] = val(substr(this.value,at('.',this.value,1)+1,at('.',this.value,2)-at('.',this.value,1)))             form.employees1.params["mm2"] = val(substr(this.value,at('.',this.value,1)+1,at('.',this.value,2)-at('.',this.value,1)))             form.employees1.params["yy1"] = val(substr(this.value,at('.',this.value,2)+1))             form.employees1.params["yy2"] = val(substr(this.value,at('.',this.value,2)+1))          elseif   at('.',this.value,1) > 0 and len(this.value) >= 7 //month and year             form.employees1.params["mm1"] = val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["mm2"] = val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["yy1"] =  val(substr(this.value,at('.',this.value,1)+1))             form.employees1.params["yy2"] = val(substr(this.value,at('.',this.value,1)+1))          elseif  at('.',this.value,1) > 0 // day and month             form.employees1.params["dd1"] =  val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["dd2"] =  val(substr(this.value,1,at('.',this.value,1)-1))             form.employees1.params["mm1"] = val(substr(this.value,at('.',this.value,1)+1))             form.employees1.params["mm2"] = val(substr(this.value,at('.',this.value,1)+1))          endif            form.employees1.requery()       endif         return    function form_onOpen()       form.text1.text = "Enter dd.mm or mm.yyyy or yyyy or dd.mm.yyyy then press Enter"       form.text1.text += chr(13)+chr(10)+chr(13)+chr(10)       form.text1.text += "Pressing Enter with no value will return all records. "       form.text1.text += chr(13)+chr(10)+chr(13)+chr(10)       form.text1.text += "Single digit days and months can be entered as a single digit or with a leading 0.  "       form.text1.text += chr(13)+chr(10)+chr(13)+chr(10)       form.text1.text += "Only digits, point, Enter and Backspace will be accepted. "       form.entryfield1.setFocus()       return           function default_params       form.employees1.params["dd1"] = 0       form.employees1.params["dd2"] = 31       form.employees1.params["mm1"] = 0       form.employees1.params["mm2"] = 12       form.employees1.params["yy1"] = 0       form.employees1.params["yy2"] = 3000       return   endclass