Unable to Programically Total Values

[David C. Holley]

I have got a report where the records extend beyond the ranges of the report
period. So if the report period is 3/1 - 3/31, you might have the following
records in the report. Since we are only concerned about time frame that the
records fall within the report, I am manipulating the report to adjust the
Start or End that is DISPLAYED on the report

ACTUAL RECORDS
RecordId Start End
1001 2/25 3/15
1002 3/15 3/31
1003 3/25 4/25

RECORDS AS DISPLAYED
RecordId Start End
1001 3/1 3/15
1002 3/15 3/31
1003 3/25 3/31

Because of this, I am having to programically calculate the duration to
adjust for situations where the date extends outside of the report range.

The problem that I am having is that totals for the report that I am
calculating are not adding up when I add them up manually. The individual
detail records are correct mathmatically, but the value of the total is not
correct.

The total is being obtained using a variable at the module level that is
increased as the Detail_Format event occurs.

And yes, I know that the dates are hardcoded it is a temporary fix until I
can fully automate the report. The (+1) is there because we are dealing with
actual calendar dates, not the number of days in between the dates.

Option Compare Database
Option Explicit
Dim mReportTitle As String
Dim TotalNumberOfDaysForReport As Long
Dim TotalForPage As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Working fine
'MinOfdteTrailerLoadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerLoadDate which is
If Me.MinOfdteTrailerLoadDate < #3/1/2010# Then
Me.dspTrailerLoadDate = "3/1/10"
Else
Me.dspTrailerLoadDate = Me.MinOfdteTrailerLoadDate
End If

'Working fine
'MaxOfdteTrailerUnloadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerUnoadDate which is
If Me.MaxOfdteTrailerUnloadDate > #3/31/2010# Then
Me.dspTrailerUnloadDate = "3/31/10"
Else
Me.dspTrailerUnloadDate = Me.MaxOfdteTrailerUnloadDate
End If

'Calculate the values only once in the event that the section is
formated more than once
If FormatCount = 1 Then
'Number of calendar days
dspExprTotalNumberOfDays = DateDiff("d",
CDate(Me.dspTrailerLoadDate), CDate(Me.dspTrailerUnloadDate)) + 1
'Should be calculating a running total over the detail here
TotalNumberOfDaysForReport = TotalNumberOfDaysForReport +
dspExprTotalNumberOfDays
'Should be calculating a running total over the page here (temporary
for debuging)
TotalForPage = TotalForPage + dspExprTotalNumberOfDays
End If

End Sub
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

'Show the total for the page (temporary)
If FormatCount = 1 Then
Me.Test = TotalForPage
TotalForPage = 0
End If

End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

Me.dspTotalNumberOfDaysForAllTrailers = TotalNumberOfDaysForReport

End Sub

 

[John Spencer]

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page totals
then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David C. Holley]

I ran across a problem on a related report that now makes me suspect that
all of the totals displayed on the report are in fact correct, but that the
grouping and formating aren't showing the values that add up to the total at
the end. The easiest way to explain my hunch is that the detail and group
sections may be displaying totals based on unique records whereas the total
at the end is based on all records regardless if they're unique or not.
Given the data, its a plausible explanation since there are only a handful
of records with duplicate data (which is a valid state by the way).

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have got a report where the records extend beyond the ranges of the
report
period. So if the report period is 3/1 - 3/31, you might have the
following
records in the report. Since we are only concerned about time frame that
the
records fall within the report, I am manipulating the report to adjust
the
Start or End that is DISPLAYED on the report

ACTUAL RECORDS
RecordId Start End
1001 2/25 3/15
1002 3/15 3/31
1003 3/25 4/25

RECORDS AS DISPLAYED
RecordId Start End
1001 3/1 3/15
1002 3/15 3/31
1003 3/25 3/31

Because of this, I am having to programically calculate the duration to
adjust for situations where the date extends outside of the report range.

The problem that I am having is that totals for the report that I am
calculating are not adding up when I add them up manually. The individual
detail records are correct mathmatically, but the value of the total is
not
correct.

The total is being obtained using a variable at the module level that is
increased as the Detail_Format event occurs.

And yes, I know that the dates are hardcoded it is a temporary fix until
I
can fully automate the report. The (+1) is there because we are dealing
with
actual calendar dates, not the number of days in between the dates.

Option Compare Database
Option Explicit
Dim mReportTitle As String
Dim TotalNumberOfDaysForReport As Long
Dim TotalForPage As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Working fine
'MinOfdteTrailerLoadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerLoadDate which is
If Me.MinOfdteTrailerLoadDate < #3/1/2010# Then
Me.dspTrailerLoadDate = "3/1/10"
Else
Me.dspTrailerLoadDate = Me.MinOfdteTrailerLoadDate
End If

'Working fine
'MaxOfdteTrailerUnloadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerUnoadDate which is
If Me.MaxOfdteTrailerUnloadDate > #3/31/2010# Then
Me.dspTrailerUnloadDate = "3/31/10"
Else
Me.dspTrailerUnloadDate = Me.MaxOfdteTrailerUnloadDate
End If

'Calculate the values only once in the event that the section is
formated more than once
If FormatCount = 1 Then
'Number of calendar days
dspExprTotalNumberOfDays = DateDiff("d",
CDate(Me.dspTrailerLoadDate), CDate(Me.dspTrailerUnloadDate)) + 1
'Should be calculating a running total over the detail here
TotalNumberOfDaysForReport = TotalNumberOfDaysForReport +
dspExprTotalNumberOfDays
'Should be calculating a running total over the page here (temporary
for debuging)
TotalForPage = TotalForPage + dspExprTotalNumberOfDays
End If

End Sub
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

'Show the total for the page (temporary)
If FormatCount = 1 Then
Me.Test = TotalForPage
TotalForPage = 0
End If

End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)

Me.dspTotalNumberOfDaysForAllTrailers = TotalNumberOfDaysForReport

End Sub

 

[David C. Holley]

The SUM won't work because I'm altering the Start & End Dates in code to
adjust them if either falls outside of the date range for the report.
So 2/15 - 3/15 becomes 3/1 - 3/15, 3/15 - 4/15 becomes 3/15 - 3/31 and
2/15 - 4/15 would become 3/1 - 3/31.

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have got a report where the records extend beyond the ranges of the
report
period. So if the report period is 3/1 - 3/31, you might have the
following
records in the report. Since we are only concerned about time frame that
the
records fall within the report, I am manipulating the report to adjust
the
Start or End that is DISPLAYED on the report

ACTUAL RECORDS
RecordId Start End
1001 2/25 3/15
1002 3/15 3/31
1003 3/25 4/25

RECORDS AS DISPLAYED
RecordId Start End
1001 3/1 3/15
1002 3/15 3/31
1003 3/25 3/31

Because of this, I am having to programically calculate the duration to
adjust for situations where the date extends outside of the report range.

The problem that I am having is that totals for the report that I am
calculating are not adding up when I add them up manually. The individual
detail records are correct mathmatically, but the value of the total is
not
correct.

The total is being obtained using a variable at the module level that is
increased as the Detail_Format event occurs.

And yes, I know that the dates are hardcoded it is a temporary fix until
I
can fully automate the report. The (+1) is there because we are dealing
with
actual calendar dates, not the number of days in between the dates.

Option Compare Database
Option Explicit
Dim mReportTitle As String
Dim TotalNumberOfDaysForReport As Long
Dim TotalForPage As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Working fine
'MinOfdteTrailerLoadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerLoadDate which is
If Me.MinOfdteTrailerLoadDate < #3/1/2010# Then
Me.dspTrailerLoadDate = "3/1/10"
Else
Me.dspTrailerLoadDate = Me.MinOfdteTrailerLoadDate
End If

'Working fine
'MaxOfdteTrailerUnloadDate is not visible in the report, the value is
pulled and
'placed into dspTrailerUnoadDate which is
If Me.MaxOfdteTrailerUnloadDate > #3/31/2010# Then
Me.dspTrailerUnloadDate = "3/31/10"
Else
Me.dspTrailerUnloadDate = Me.MaxOfdteTrailerUnloadDate
End If

'Calculate the values only once in the event that the section is
formated more than once
If FormatCount = 1 Then
'Number of calendar days
dspExprTotalNumberOfDays = DateDiff("d",
CDate(Me.dspTrailerLoadDate), CDate(Me.dspTrailerUnloadDate)) + 1
'Should be calculating a running total over the detail here
TotalNumberOfDaysForReport = TotalNumberOfDaysForReport +
dspExprTotalNumberOfDays
'Should be calculating a running total over the page here (temporary
for debuging)
TotalForPage = TotalForPage + dspExprTotalNumberOfDays
End If

End Sub
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

'Show the total for the page (temporary)
If FormatCount = 1 Then
Me.Test = TotalForPage
TotalForPage = 0
End If

End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)

Me.dspTotalNumberOfDaysForAllTrailers = TotalNumberOfDaysForReport

End Sub

 

[John Spencer]

The SUM should work.

Note that I've built in two IIF statements to adjust the dates used in the
calculation. I used hard-coded dates for the cutoff, but if the hardcoded
dates are available in your record source (as parameters or fields) then you
can refer to them in the expression.

This expression returns the later of the Start date or 3/1/2010.
IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
and this expression returns the earlier of End date or 3/31/2010
IIF([End]>#3/31/2010#,#3/31/2010#,[End])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The SUM won't work because I'm altering the Start & End Dates in code to
adjust them if either falls outside of the date range for the report.
So 2/15 - 3/15 becomes 3/1 - 3/15, 3/15 - 4/15 becomes 3/15 - 3/31 and
2/15 - 4/15 would become 3/1 - 3/31.

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[microsoft]

Got a time machine so that I can go back and ask myself this question back
in 1998? Its been about that long since I've had to deal wtih Reports.

The SUM should work.

Note that I've built in two IIF statements to adjust the dates used in the
calculation. I used hard-coded dates for the cutoff, but if the hardcoded
dates are available in your record source (as parameters or fields) then
you can refer to them in the expression.

This expression returns the later of the Start date or 3/1/2010.
IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
and this expression returns the earlier of End date or 3/31/2010
IIF([End]>#3/31/2010#,#3/31/2010#,[End])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The SUM won't work because I'm altering the Start & End Dates in code to
adjust them if either falls outside of the date range for the report.
So 2/15 - 3/15 becomes 3/1 - 3/15, 3/15 - 4/15 becomes 3/15 - 3/31 and
2/15 - 4/15 would become 3/1 - 3/31.

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[microsoft]

I think that I got it. I added a textbox to the report who's control source
is set to the unbound textbox that is displaying the total for the adjusted
dates. I then set the new textbox to RUNNING SUM to OVER ALL.

The SUM should work.

Note that I've built in two IIF statements to adjust the dates used in the
calculation. I used hard-coded dates for the cutoff, but if the hardcoded
dates are available in your record source (as parameters or fields) then
you can refer to them in the expression.

This expression returns the later of the Start date or 3/1/2010.
IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
and this expression returns the earlier of End date or 3/31/2010
IIF([End]>#3/31/2010#,#3/31/2010#,[End])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The SUM won't work because I'm altering the Start & End Dates in code to
adjust them if either falls outside of the date range for the report.
So 2/15 - 3/15 becomes 3/1 - 3/15, 3/15 - 4/15 becomes 3/15 - 3/31 and
2/15 - 4/15 would become 3/1 - 3/31.

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[microsoft]

That did the trick.

I think that I got it. I added a textbox to the report who's control source
is set to the unbound textbox that is displaying the total for the adjusted
dates. I then set the new textbox to RUNNING SUM to OVER ALL.

The SUM should work.

Note that I've built in two IIF statements to adjust the dates used in
the calculation. I used hard-coded dates for the cutoff, but if the
hardcoded dates are available in your record source (as parameters or
fields) then you can refer to them in the expression.

This expression returns the later of the Start date or 3/1/2010.
IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
and this expression returns the earlier of End date or 3/31/2010
IIF([End]>#3/31/2010#,#3/31/2010#,[End])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The SUM won't work because I'm altering the Start & End Dates in code to
adjust them if either falls outside of the date range for the report.
So 2/15 - 3/15 becomes 3/1 - 3/15, 3/15 - 4/15 becomes 3/15 - 3/31 and
2/15 - 4/15 would become 3/1 - 3/31.

I would just calculate the total in a control in the report footer.

=SUM(DateDiff("D",IIF([Start]<#3/1/2010#,#3/1/2010#,[Start])
,IIF([End]>#3/31/2010#,#3/31/2010#,[End]))+1)

That is a lot simpler and should be more accurate. If you need page
totals then things get more complex

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County