Subject Re: Using Field Named DATE in Where clause
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 14 Feb 2024 14:50:41 +0200
Newsgroups dbase.getting-started

On 2024/02/14 14:08, Craig wrote:
> In terms of the fieldName being a reserved word. I am trying to get new reports done in the current version of dBase as the original database is from a very early version which only works on Windows XP so the choice was not mine.
>
> Here is the Where clause that I am trying to use:
> where DATE>"(currentDate()-30)" order by DUEDATE, CUSTOMER
>
> This is what I saw online when I tried to look up how to do it


Although it is not a good idea to use reserved words as fieldnames it
can be dealt with.  Wrap the fieldname in single quotes and qualify it
by using the tablename or a table correlation name (alias).

    select * from whatever where whatever.'DATE' > currentDate() ...
or
    select * from whatever w where w.'DATE' > currentDate() ...

The problem is that current_Date() (or curdate() ) is "proper" SQL and
is not available in localSQL.

The first thing to do is to use a parameter driven query in your report.
Something like

    this.WHATEVER1 = new QUERY(this)
    with (this.WHATEVER1)
       left = 14.0
       width = 1.0
       sql = "select * from whatever w where "
       sql += "w.'date' > :ddate - 30 "
       sql += "order by duedate,customer"
       requestLive = false
       params['ddate'] = date() //Today
       active = true
    endwith

The sql property is built up here to avoid line-wrap.

Unfortunately, when the report designer streams out the .rep file,
date() will be evaluated and will be streamed out as today's date i.e
today's date is effectively hard-coded in the report.

To get round this create a little "launch" form for the report with a
pushbutton to actually run the report.  Create an instance of the report
in memory. Once the instance is available you can change ANY of its
properties before rendering it.

In the form's onOpen event handler

        function form_onOpen
           set procedure to whatever.rep
           form.rep = new whateverReport() //use classname in report
           //this creates an instance of the report in memory
           return

In the pushbutton's onClick event handler

        function PUSHBUTTON1_onClick
           form.rep.WHATEVER1.params['ddate'] = date()
           //Force the value in the report to today's date
           form.rep.WHATEVER1.requery()
           //Fetch the required records
           form.rep.render() //run the report
           return

Mervyn.