Subject Re: Preview.wfm and report printing
From trevor <bob@bob.com>
Date Thu, 02 Nov 2023 18:04:33 -0400
Newsgroups dbase.getting-started

Mervyn Bick wrote:

> On 2023/11/01 20:18, trevor wrote:
 >
> > I had already started to think on those lines and thought the
 > > following code should work. Just a simple form with on pushbutton
 > > as a test.
 > >
> > function PUSHBUTTON1_onClick
 > >                                         q = new QUERY()
 > >             fdat1 = {10.09.2023}
 > The only date format that dBASE recognises that uses a point as the
 > default date separator is German.  If your computer's date format is
 > German then that date is September 10 2023.
 >
> The default date separator for the American date format is / but
 > dBASE will accept that date as October 9 2023 even if mark has not
 > been set to a point.  dBASE will, however, display the date with
 > whatever is set for mark.  This can be important. See below.
 >
> >       q.sql = 'select * from "transfers.DBF" where transfer > fdat1'
 >
> As Ken has pointed out, fdat1 is not a field so this is giving an
 > error.
 >
> Where a date is used in a WHERE clause in a SELECT statement it must
 > either be in a field or it must be presented as a literal date.
 > "Proper" SQL requires the delimiter for a literal date to be a single
 > quote but localSQL will accept either single or double quotes.
 > Square brackets, which dBASE will also accept as the delimiters for a
 > string, are, however, not accepted.
 >
> localSQL will only accept literal dates in American (mm/dd/yyyy) or
 > German (dd.mm.yyyy) form no matter what date format is set for the
 > computer.
 >
> Ken has suggested
 >
> q.sql = 'select * from "transfers.DBF" where transfer > "' + fdat1
 > '+'"'
 >
> dBASE will auto-convert the date to a string but this will only work
 > if the computer's date format is either German or American and mark
 > has been left with its default setting.  If date is set to American
 > and mark is set to a point then localSQL will see the date as
 > September 10 2023 instead of October 9 2023.
 >
> An alternative is to use a parameter in the SELECT statement.  This
 > uses a date rather than a literal date.  Setting the date format only
 > affects display.  Internally there is no difference no matter what
 > format is used.
 >
> q = new query()
 > q.sql = 'select * from "transfers.DBF" where transfer > :fdat1'
 > q.params['fdat1'] = {10.09.2023} //See below
 > q.active = true
 >
> If your date format is American the date may need to be {10/09/2023}.
 > With the date format American, assigning {10.09.2023} to a variable
 > dBASE sees the date as October 9 2023. I'm not sure that the query
 > object's innards will do the same.
 >
>
> >                               active = true
 >
>         q.active = true
 >
> >             ?q.sql
 > >
> >        u = new UpdateSet()
 > >        u.source =q.rowset
 > >        u.destination  = "temptransfers.dbf"
 > >         if _app.databases[1].tableExists("temptransfers.dbf" )
 > >        u.append()
 > >         else
 > >         u.copy()
 > >         endif
 > >              * save to  temptransfers.dbf
 >
>        q.active = false
 >   //If you don't do this dBASE won't free up the memory used.
 >
> >       return
 > >
> > ?q.sql return the correct select, although not sure how to see the
 > > rowset or if it exists.
 >
> It will exist unless there was an error but it may be empty.
 >
> The following must be executed while the query is still open.
 >
> if q.rowset.rowcount() > 0
 >   do while not q.rowset.endofset
 >     for n = 1 to q.rowset.fields.size
 >       ?? q.rowset.fields[n].value +' '
 >     next
 >     ?
 >     q.rowset.next()
 >   enddo
 > else
 >   ? 'Rowset empty'
 > endif
 >
> >
> >  I get an error with U.append() or U.copy()
 > > "Operation not applicable both calls. The update  set() is an
 > > example from dBase Help.
 >
> The error is probably because the query wasn't made active.
 >
> > Also if I use the " save to... " I get a table but can't view it &
 > > get corrupt table/index header.
 >
> The SAVE TO option of the SELECT command is only available when
 > SELECT is used as a localSQL command.  It doesn't work if used in the
 > sql property of a query.
 >
> cmd = 'select * from "transfers.DBF" where transfer > "' + fdat1 '+'"'
 > cmd += ' save to temptransfers'
 > //?cmd //Uncomment to see command
 > &cmd
 > USE
 >
> This localSQL command creates a workarea in exactly the same way as
 > USE transfers  would do in XDML.  To close the workarea execute USE
 > without a table name.
 >
> As an aside, you can name tables and variables as you see fit but it
 > can make code easier to read if you include underscore characters in
 > tablenames.
 >
> ... save to temp_transfers
 >
> Mervyn.
Thanks for all the help.

I think I've solved it eventually. I placed on my main form  the query,
Sql select  & parameters I had in the report. Using Append() and a
temporary table.
can't think why I didn't go with parameters as before, just got lost in
trying to avoid use of temporary table.

Trevor