YTD Calc in Footer

[Guest]

I am extracting sales data from my ERP program. The sales data is stored in
an array which I break out by month. There is also a year associated with it.
Basically my sales table appears as follows:

Customer# Year Sls01 Sls02 Sls03 Sls04 etc
1234 2006 100 150 200 300 ...
1234 2007 50 25 100 400
5678 2006 0 100 25 50

And so on. Here's my problem. I want to get YTD sales and a percentage
change in my customer footer. Where I need help is that I want to
programmatically compare the first 4 months of 2007 against the first 4
months of 2006. In May I want to compare the first 5 periods. I really don't
have any date information on the data other than year. Is there a way to
accomplish this without writing a huge expression? (I can't think of a way to
do it other than a nest of 12 IF statements)

Any help is greatly appreciated.

 

[Guest]

Here is a function that calculates a YTD Value. Put it in a Standard module.
Then in the text box on the report where you want the YTD amount, call it
from the control source, using the month and the values for each month. As
an example, let's say you have the date in a text box on the report named
txtRptDate and that your controls for each month are txtSls01, txtSls02,
txtSls03, etc.

=CalcYTD(Month(Me.txtRptDate), Me.txtSls01, Me.txtSls02, Me.txtSls03,.....)
Of course, include each month


Public Function Calc_Ytd(MoNo, January, February, march, april, may, june,
july, August, _
September, October, November, December)
' Dave Hargis 8/04
' Calculates Year To Date Totals
'MoNo is the last month to include in the calculation
'For example, to include January through August, it should be 8

'Be Sure we Don't Have any Null Values
January = Nz(January, 0)
February = Nz(February, 0)
march = Nz(march, 0)
april = Nz(april, 0)
may = Nz(may, 0)
june = Nz(june, 0)
july = Nz(july, 0)
August = Nz(August, 0)
September = Nz(September, 0)
October = Nz(October, 0)
November = Nz(November, 0)
December = Nz(December, 0)

Select Case MoNo
Case 1
Calc_Ytd = January
Case 2
Calc_Ytd = January + February
Case 3
Calc_Ytd = January + February + march
Case 4
Calc_Ytd = January + February + march + april
Case 5
Calc_Ytd = January + February + march + april + may
Case 6
Calc_Ytd = January + February + march + april + may + june
Case 7
Calc_Ytd = January + February + march + april + may + june + july
Case 8
Calc_Ytd = January + February + march + april + may + june +
july + August
Case 9
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September
Case 10
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October
Case 11
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October + November
Case 12
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October + November + December

End Select