To do this, I'd create a hierarchical ADO recordset using
Provider=MSDataShape and the SHAPE syntax e.g. something like:

SHAPE {SELECT Year FROM EarningsHistory} AS Parent
APPEND ({SELECT Year, Date, Amount FROM EarningsHistory} AS Child
RELATE Year TO Year)

The advantage of this approach for VB6 is you can use the hierarchical
recordset as the DataSource property of a Data Report.

--

kerang wrote in message ...
Hi Trevor,

Thanks for the guidance, but i assumed that i have to insert the formula into the excel worksheet. How can i apply it in VB6 anyway? Can i use the same formula maybe something like;

wsXl.cells(2,1).FormulaLocal= "="YEAR & cells(2,2)" ---- for cell A2
and
wsXl.cells(3,1).formulaLocal = "=if"(YEAR & cells(3,2)< YEAR & cells(2,2), YEAR & cells(3,2), "" "

----- Trevor Shuttleworth wrote: -----

Kerang

assuming your dates are in column B and your amounts are in column C ... and
that the headings are in row 1:

The first date will be in Cell B2, the first amount in Cell C2 ...

in Cell A2, put the formula: =YEAR(A2)

in cell A3, put the formula: =IF(YEAR(B3)<YEAR(B2),YEAR(B3),"")

Now drag down on the fill handle to repeat the formula

Regards

Trevor


"kerang" wrote in message
...
hi there,
Hope that someone could help me on this. I have a problem in creating an
accounting report in VB6. My report will look like this.
Year Date Amount
2000 17/02/00 2000
18/02/00 3000
2001 20/02/01 500
29/03/01 6000
30/04/01 7000
My database will only have date and amount fields.The problem is how to
avoid the Year value from repeating.