Subject Re: Preview.wfm and report printing From Mervyn Bick <invalid@invalid.invalid> Date Thu, 2 Nov 2023 14:35:35 +0200 Newsgroups dbase.getting-started 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.