Subject Re: Calculated field and Query
From Mervyn Bick <invalid@invalid.invald>
Date Thu, 13 Dec 2018 11:39:38 +0200
Newsgroups dbase.getting-started

On 2018-12-12 8:53 PM, Dirk wrote:
> Hello, Mervyn and tim
> The way tim proposed; because the feilds get a fixed place (example :
> oEXCcel.range("d11").select()
>      oExcel.ActiveCell.FormulaR1C1 = "km"
> the loop only gives teh last values
>   i suppose of the range
>   if you oly take the first row : its works
> about Mervyn
> the works i only haveto get the info in colomms its belong,
> its a matter to put time on this question
> thanks for the other ways
> " difficult way is also a way"

Once you understand it, it's actually easier. :-)

The trick is to get the fields in the rowset in the correct order.  As
you have found, instead of using the * wild card you can specify the
fields in whatever order you want.  You can place calculated fields
anywhere in the field list.  If necessary a calculated field can be
before or between the fields used in the calculation.

If you have many fields with fairly long names you can save yourself a
lot of work by using the SQL designer to set up the SELECT statement.
You can select which fields you want and drag them up or down in the
designer's output panel into the order you want.  Use the button with
three dots to the left of each field to drag it up or down.  In the
sourcecode editor add your calculated fields in the correct places.  In
your query instead of typing out your SELECT statement use sql =

You can, of course, simply place the values from the field in the
spreadsheet and then make the spreadsheet calculate the values for the
calculated columns.  This means skipping columns in the right places or
writing code to insert columns.  Then you have to write code to place
the correct formula for each calculated value.  It can be done but it
will be a LOT more complicated than simply including the calculated
values in the SQL SELECT statement in the first place.

A cell in Excel can be specified as an absolute e.g oCell.range("d11")
but it can also be specified by giving a row and column.  Instead of
oExcel.range("d11").select()  you can use oExcel.range(11,4).select()

The row and column can be held in variables e.g  nRow = 11 nCol = 4
(Column D) then oExcel.range(nRow,nCol).select()

This second way of specifying cells makes it easy to loop through fields
in a record and place the values in adjacent cells in the spreadsheet.

qrf = q.rowset.fields // save some typing in the loop
nRow = 11
do while not q.rowset.endofset
    nRow++ // Move down a row for each record
    nCol = 8 // Start in column H for each record
    for n = 1 to qrf.size //for each field in the rowset
       oCell = oSheet.cells(nRow,nCol)
       oCell.formula = qrf[n].value
       nCol++ // move to next column for the next field
     next //Go to next record