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 Amoun
2000 17/02/00 200
18/02/00 300
2001 20/02/01 50
29/03/01 600
30/04/01 700

My database will only have date and amount fields.The problem is how to avoid the Year value from repeating.

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.

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 A
and
wsXl.cells(3,1).formulaLocal = "=if"(YEAR & cells(3,2)< YEAR & cells(2,2), YEAR & cells(3,2), "" "

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

Keran

assuming your dates are in column B and your amounts are in column C ... an
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 formul

Regard

Trevo


"kerang" wrote in messag
..
hi there
Hope that someone could help me on this. I have a problem in creating a
accounting report in VB6. My report will look like this
Year Date Amoun
2000 17/02/00 200
18/02/00 300
2001 20/02/01 50
29/03/01 600
30/04/01 700
My database will only have date and amount fields.The problem is how t
avoid the Year value from repeating

Kerang

I think that would be:

wsXl.Cells(2, 1).FormulaLocal = "=Year(B2)" '---- for cell A2
wsXl.Cells(3, 1).FormulaLocal = "=IF(Year(B3)<Year(B2),Year(B3),"""")"

Regards

Trevor


"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.

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.