Last 2 Dates - calc in report not query

[BHScripter]

Hi,

I have a report which is grouped first by account number and then under that is the cost center. I am getting the monthly average of each cost center for that account.

example:

Account 700000 (telecommunications) (visible)
Cost Center "Builders" (visible)
01/03 $10 (this monthly detail is not visible)
02/03 $10
04/03 $10

Total 30 average 10 (visible)

I need to also show the latest two months dates as well as the actuals for those two months.

I was able to get the max month by using an unbound text box to get the max date.

However, I can't seem to get the next month in line. Also, there may be some months where there were no charges. I need the latest two months with charges. Can I do this in the report rather than in a query since my report contains all the detail it just isn't visible?

Thanks,
Brianna

 

[danvlas]

Sort the detail section by month.
In the group header/footer create calculated text boxes and link them to the data in the detail section.
=[TextBoxInDetailSection]

Such textboxes will display the value in the last detail line (last formatted data).

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BHScripter]

That works great for the last record but how do I get the one just before it?

I'm sure it's probably very simple, but I'm not seeing it.

Thanks,
Brianna

 

[danvlas]

Hmmm...
I'd think of an array with two elements.
In the Detail format section:

Array(0) = Array(1)
Array(1) = YourValue

When the detail formatting is over, you have the 2 last formatted elements in the array and you can use the values in the Format event of a group section to fill in the appropriate unbound boxes.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html