Calculated value is Twice Expected value 2

[chtullu]

Hello,

I need to create a report where in addition to daily grandtotals, I have to output daily grand totals for all locations that are "Health Connect". When running the report, I find that while my daily grandtotals for the entire day are correct, my daily Health Connect grandtotals are twice the expected value. When I put a msgbox code in the location footer to output the location, each location appears twice in the msgbox output. I'm not sure what's going on, but I think it has something to do with the format count property

Option Compare Database
Option Explicit
Dim dblDailyTotal As Double

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

Me.txtHCDailyTotal = dblDailyTotal

End Sub

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

Dim intLocCounter As Integer, strLocation As String
strLocation = Mid(Me.Location, 1, 14)

If strLocation = "Health Connect" Then

dblDailyTotal = Me.Total_Amount + dblDailyTotal

End If


End Sub

Private Sub LocationFooter_Format(Cancel As Integer, FormatCount As Integer)
MsgBox "Location " & Me.Location
End Sub

 

[dhookom]

You should be able to delete all code and use a control source of:

=Sum(Abs(Left([Location],14)="Health Connect")*[AmountField])

Your experience may vary since we don't know where Total_Amount comes from.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RoyVidar]

Or, if code is still interesting, the FormatCount parameter/arguement could probably be used.

[tt]If strLocation = "Health Connect" and formatcount = 1 Then
dblDailyTotal = Me.Total_Amount + dblDailyTotal
End If[/tt]

Roy-Vidar

 

[chtullu]

Thanks dhookom,

Your suggestion worked fine. Thank you also RoyVidar. I do have a question about the code solution although I decided to go with the former suggestion. I put the code into the location footer and then put
Me.Total_Amount = dblDailyTotal in the date footer
I kept getting the wrong totals. I'm pretty sure that

If strLocation = "Health Connect" and formatcount = 1 Then
dblDailyTotal = Me.Total_Amount + dblDailyTotal
End If

goes into the location footer
Where does Me.Total_Amount = dblDailyTotal belong. Perhaps I have to initialize dblDailyTotal to zero for each date.