Search Substring of Date field?

[MoGrace]

I am using variables on a form to search the primary table of a form - in an embedded browse, but am having trouble getting the filter to work for the date field. I have an index CRECDATE defined as cdate(RECDATE) which seems to me that if I put that index in place, the user should be able to search for "2010" as a year, or "07" as either a month or day to find as a substring in the field. Here is the portion I am working on:

vFilter = "(Nsi_id = "+quote(alltrim(var->vCustList))+")"

if searchby = "RECDATE"
 if isdate(alltrim(searchfor))
   '(Nsi_id = "FT"  )  .and. (Recdate = ctod("10/01/2008")  )
     vFilter = EVAL(vFilter + ".and. (recdate = " + s_quote(ctod(var->searchfor))+")") 
    else
     searchby = "Crecdate"
     vFilter = EVAL(vFilter + ".and.(" + quote(Var->searchfor)+"$"+cdate(var->searchby)+")") 
    end if
end if

[Keith Hubert]

Re: Search Substring of Date field?

Hi Robin,

Have you looked at the other functions that break up a date such as, month() and Year()?

[Tim Kiebert]

Re: Search Substring of Date field?

I think
vFilter = EVAL(vFilter + ".and.(" + quote(Var->searchfor)+"$"+cdate(var->searchby)+")")

should be

vFilter = EVAL(vFilter + ".and.(" + quote(Var->searchfor)+"$cdate(recdate)")

or

vFilter = EVAL(vFilter + ".and.(" + quote(Var->searchfor)+"$cdate("+eval(var->searchby)+"))")

[MoGrace]

Re: Search Substring of Date field?

My problem was that the var->searchby was for getting the fieldname and quoting it did not work in the filter. Whereas the text to find in the var->searchfor did need to be formatted with quote() or s_quote() depending on data type. I finally gave up on using the genie generated table.query_run() and went back to table.order().

This app is used for getting a text report from a DOS app and converting it into a table that can be queried and modified for doing physical inventory - things which cannot be done in the DOS app. In this screenshot, all the inventory records that are less than 40 lbs are displayed - which is not enough to produce a roll of fabric. Notice the filter also displays at the bottom of the form - which should help the user see what his query is doing. The Trace window now shows the result of the correct filter too whether the data searched is character, date or numeric.