Running Total in Detail OnFormat doesn't match total on Group Footer 1

[xicana]

I have a report that list's dates for each record. For each group I want to know how many records have Jan date, feb date, mar date...etc. Instead of creating all these text boxes and making the running totals, I thought of putting it in the back end with VBA...the problem is the code works some times and some times it doesn't. I believe it has to do with the OnFormat property that I'm using...the problem seems to arise when there's a page break...

Option Compare Database
'define variable's for keeping the monthly tallys

    Dim JanCount As Integer
    Dim FebCount As Integer
    Dim MarCount As Integer
    Dim AprCount As Integer
    Dim MayCount As Integer
    Dim JunCount As Integer
    Dim JulCount As Integer
    Dim AugCount As Integer
    Dim SepCount As Integer
    Dim OctCount As Integer
    Dim NovCount As Integer
    Dim DecCount As Integer
    Dim intMonth As Integer
    
    


     

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'for each detail line, execute the following code.  Keep a running total over the group
'we will reset the group total on the group header (make sure you have set the properties
'not to repeat the section

intMonth = DatePart("m", Me!ADMITDT)  'define what calculates intmonth


Select Case intMonth
'keep the running total depending on the month of admission
Case 1
    JanCount = JanCount + 1
Case 2
    FebCount = FebCount + 1
Case 3
    MarCount = MarCount + 1
Case 4
    AprCount = AprCount + 1
Case 5
    MayCount = MayCount + 1
Case 6
    JunCount = JunCount + 1
Case 7
    JulCount = JulCount + 1
Case 8
    AugCount = AugCount + 1
Case 9
    SepCount = SepCount + 1
Case 10
    OctCount = OctCount + 1
Case 11
    NovCount = NovCount + 1
Case 12
    DecCount = DecCount + 1

End Select

End Sub



Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    Me!txtJanTotal = JanCount
    Me!txtFebTotal = FebCount
    Me!txtMarTotal = MarCount
    Me!txtAprTotal = AprCount
    Me!txtMayTotal = MayCount
    Me!txtJunTotal = JunCount
    Me!txtJulTotal = JulCount
    Me!txtAugTotal = AugCount
    Me!txtSepTotal = SepCount
    Me!txtOctTotal = OctCount
    Me!txtNovTotal = NovCount
    Me!txtDecTotal = DecCount
    Me!txtREQPROVTOTAL = JanCount + FebCount + MarCount + AprCount + MayCount + JunCount + JulCount + AugCount + SepCount + OctCount + NovCount + DecCount
End Sub


Private Sub GroupHeader3_Format(Cancel As Integer, FormatCount As Integer)
    ResetCounts
    
End Sub


Public Sub ResetCounts()
    JanCount = 0
    FebCount = 0
    MarCount = 0
    AprCount = 0
    MayCount = 0
    JunCount = 0
    JulCount = 0
    AugCount = 0
    SepCount = 0
    OctCount = 0
    NovCount = 0
    DecCount = 0
    
End Sub

I will get different counts for each month if I RESIZE the detail section...isn't that strange? Does someone know why this is happening?

Sandy

 

[dhookom]

This is happening because a record may attempt to format on one page but then actually be formatted a second time and placed on the next page.

I never run code to aggregate values in reports. Why not just use running sums over groups?

Duane
Hook'D on Access
MS Access MVP

 

[xicana]

Make sense.

I was trying to be clever...lol...If I use running sums then I would have to have a text box for each month...correct? (making it not visible of course).

Thanks.

Sandy

 

[dhookom]

I'm not sure what you are attempting to accomplish. If you want counts by month to place in a group footer, the most robust method would probably be to create a totals query similar to your report's record source but grouping and counting by month. Then create a subreport based on this totals query and placing it in the group footer.

You can also count records meeting a condition in a group section with an expression like:

   =Sum(Abs({Your Expression Here})

If you want to count the number of ADMITDT dates in January, your expression would be:

   =Sum(Abs(Month(ADMITDT)=1))

Duane
Hook'D on Access
MS Access MVP

 

[xicana]

The last example was perfect!

thanks.

Sandy