Planned v Actual Revenue Rpt...

[Lissa]

I have created a Planned vs Actual Revenue Report which displays planned and actual revenue horizontally as follows
................ JAN FEB MAR QTR1 ...... OCT NOV DEC QTR4 ANNUAL
Planned $
Actual $
Variance
Variance %

The Planned Revenue has already been entered for the entire year.
Actual Revenue has only been entered up to May.

The ANNUAL field contained the revenue sum of each quarter but then I realized if a manager is looking at this it won't exactly make sense since we have not reached the 4th quarter of 2007. The ANNUAL field should only contain the sum of QTR 1 and QTR 2 or even more accurately the sum of Jan-May for now.
I would love to be able to write some code to handle this field but where can I put it? The expression builder of a text field won't handle it... or at least I don't think it will...
I used the IIf function to place 0 in the variance and variance field if there are no actuals reported. But I'm not sure if IIf will achieve what I need in the ANNUAL field.
Does anyone have any ideas?

Thanks

 

[lark_tilly]

In the Detail_Format code section of the rpt enter something like this.
The jan, feb, etc should match up to your field's correct names.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim sglActual As Single
sglActual = 0
If jan <> 0 Then sglActual = sglActual + jan
If feb <> 0 Then sglActual = sglActual + feb
If mar <> 0 Then sglActual = sglActual + mar
'so on and so forth thru dec
'leave out the qtrly fields
annual = sglActual

End Sub

Hope this helps!

 

[Lissa]

Oh so that's where you can put the code - the On Format area of the Detail Section! Learning something everyday

Your code snippet worked like a charm!
Thanks!!
Lissa

 

[lark_tilly]

Great, glad I could help!
A little code can make a big difference in the way reports work.