Page totals error in report - doesn't add correctly

[Geoff]

I am trying to print the total number of hours billed to a client in a
report. Initially this was displayed in a textbox with the source set to the
expression "=Sum([Hours]). This works fine on the one report which displays
the decimals correctly in the report footer, but won't work at all on the
other report where the total should read 266.85 hours but instead will only
display 266 hours.

Then I used Allen Browne's Page Total code and discovered that the problem
is caused by the total hours for the first page which are displayed
incorrectly (adds up to 10 whereas it should be 10.83). The total hours all
display correctly in the group footers but won't do so in the report footer.
All Totals text boxes properties are set to General number with 2 decimal
places - as is the data in the source query and table.

Why does one report work fine and the other not? I've even completely
rebuilt a new report from the one that works, but to no avail - still gives
the wrong total. Can anyone help? Thanks

Allen Browne's code that I used was as follows:

Option Compare Database
Dim HoursTotal As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then HoursTotal = HoursTotal + Nz(Me.Hours, 0)
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageTotal = HoursTotal
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
HoursTotal = 0
End Sub

 

[Marshall Barton]

I am trying to print the total number of hours billed to a client in a
report. Initially this was displayed in a textbox with the source set to the
expression "=Sum([Hours]). This works fine on the one report which displays
the decimals correctly in the report footer, but won't work at all on the
other report where the total should read 266.85 hours but instead will only
display 266 hours.

Then I used Allen Browne's Page Total code and discovered that the problem
is caused by the total hours for the first page which are displayed
incorrectly (adds up to 10 whereas it should be 10.83). The total hours all
display correctly in the group footers but won't do so in the report footer.
All Totals text boxes properties are set to General number with 2 decimal
places - as is the data in the source query and table.

Why does one report work fine and the other not? I've even completely
rebuilt a new report from the one that works, but to no avail - still gives
the wrong total. Can anyone help? Thanks

Allen Browne's code that I used was as follows:

Option Compare Database
Dim HoursTotal As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then HoursTotal = HoursTotal + Nz(Me.Hours, 0)
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageTotal = HoursTotal
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
HoursTotal = 0
End Sub

That code that you're using isn't supposed to do what you
say you want. I suspect that there may be more to using
that code than just copying it, because there are several
caveats to using an event procedure to calculate a total.
In almost all cases that code can not reliably calculate a
correct total.

Using the Sum function is the right way to do this if Hours
is a field in the report's recordsource table/query.
(Otherwise, you can use a RunningSum text box to add up the
total.)

The first thing I would suspect is that the Hours is somehow
being converted to an integer. To check this, try setting
the Hours text box in the detail section and all the group
and report footer text boxes to use the format 0.000000000
so you can see the whole value.

The next thing to check is the actual, not the formatted,
values in the recordsource table/query. Applying a format
to the table and query just obscurs the values used in the
calculations.

 

[Geoff]

Hi Marshall

I've tried everything you suggested to no avail, but I have identified that
the root cause of the problem is in the table.

What I do is consolidate the data from a number of Excel time-sheets into a
single workbook. In Excel the Hours column is expressed as 08:20:00. I set
the format for this column to General, multiply by 24 and then use paste
special which converts the value in the applicable cell in the Hours coulmn
to 8.333333333.

I then import the Excel spreadsheet into a table called Import. The then
filter the data in the Import table (sort columns etc) and run a Make Table
query based on the Import Table. The problem is that in both the import table
and the Query table, while the imported value displays correctly as
8.333333333, and the Hours field is set to a Number data type, I'm sure
Access still thinks it is a time data type and therefore can't Total
correctly when using the =Sum([Hours]) function on the report.

My reason for this thinking is that when I try to insert the expression
CCur(#0.00#) for the Hours field in the Make Table query by using the
Expression Builder, Access automatically shows the Criteria value in the
design view of the query to be CCur(#00:00:00#) and not CCur(#0.00#) as
entered. (I'm using the CCur conversion instead of the CDbl conversion to try
and restrict the value to 2 decimals).

Any thoughts on how to build an expression in the Make Table query that will
somehow convert the 8.333333333 time value (although shown as a Number data
type) to a numeric or currency value that I can use in adding up my total
Hours correctly?

I am trying to print the total number of hours billed to a client in a
report. Initially this was displayed in a textbox with the source set to the
expression "=Sum([Hours]). This works fine on the one report which displays
the decimals correctly in the report footer, but won't work at all on the
other report where the total should read 266.85 hours but instead will only
display 266 hours.

Then I used Allen Browne's Page Total code and discovered that the problem
is caused by the total hours for the first page which are displayed
incorrectly (adds up to 10 whereas it should be 10.83). The total hours all
display correctly in the group footers but won't do so in the report footer.
All Totals text boxes properties are set to General number with 2 decimal
places - as is the data in the source query and table.

Why does one report work fine and the other not? I've even completely
rebuilt a new report from the one that works, but to no avail - still gives
the wrong total. Can anyone help? Thanks

Allen Browne's code that I used was as follows:

Option Compare Database
Dim HoursTotal As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then HoursTotal = HoursTotal + Nz(Me.Hours, 0)
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageTotal = HoursTotal
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
HoursTotal = 0
End Sub

That code that you're using isn't supposed to do what you
say you want. I suspect that there may be more to using
that code than just copying it, because there are several
caveats to using an event procedure to calculate a total.
In almost all cases that code can not reliably calculate a
correct total.

Using the Sum function is the right way to do this if Hours
is a field in the report's recordsource table/query.
(Otherwise, you can use a RunningSum text box to add up the
total.)

The first thing I would suspect is that the Hours is somehow
being converted to an integer. To check this, try setting
the Hours text box in the detail section and all the group
and report footer text boxes to use the format 0.000000000
so you can see the whole value.

The next thing to check is the actual, not the formatted,
values in the recordsource table/query. Applying a format
to the table and query just obscurs the values used in the
calculations.

 

[Marshall Barton]

First, you should not try to round, convert or otherwise
mess with the value in the field until you are ready to
display it on a form or report. Using CCur will truncate
the value to four decimal places, which may introdudce small
errors that could accumulate to something significant.

The big conceptual problen is that your use of #0.00# is
forcing a datetime value of 0, which is midnight. If you
want 0 hours, then just write a 0. Don't be confused by the
fact that you are trying to express an elapsed time in hours
with a date/time value. The hours field is NOT a date/time,
it is no different than saying 8.33 feet of material. Since
you are importing the value as a double that is a fraction
of a day, multiplying by 24 is the right way to scale the
number to hours. After that, don't do anything other than
add the values together.

Note that saying that the table field is a number type just
says it's not text or date. It is the Size of the field
that determines what kind of number you are using (Integer,
Long, Double, etc). Make sure your field is a Double.
--
Marsh
MVP [MS Access]

I've tried everything you suggested to no avail, but I have identified that
the root cause of the problem is in the table.

What I do is consolidate the data from a number of Excel time-sheets into a
single workbook. In Excel the Hours column is expressed as 08:20:00. I set
the format for this column to General, multiply by 24 and then use paste
special which converts the value in the applicable cell in the Hours coulmn
to 8.333333333.

I then import the Excel spreadsheet into a table called Import. The then
filter the data in the Import table (sort columns etc) and run a Make Table
query based on the Import Table. The problem is that in both the import table
and the Query table, while the imported value displays correctly as
8.333333333, and the Hours field is set to a Number data type, I'm sure
Access still thinks it is a time data type and therefore can't Total
correctly when using the =Sum([Hours]) function on the report.

My reason for this thinking is that when I try to insert the expression
CCur(#0.00#) for the Hours field in the Make Table query by using the
Expression Builder, Access automatically shows the Criteria value in the
design view of the query to be CCur(#00:00:00#) and not CCur(#0.00#) as
entered. (I'm using the CCur conversion instead of the CDbl conversion to try
and restrict the value to 2 decimals).

Any thoughts on how to build an expression in the Make Table query that will
somehow convert the 8.333333333 time value (although shown as a Number data
type) to a numeric or currency value that I can use in adding up my total
Hours correctly?

I am trying to print the total number of hours billed to a client in a
report. Initially this was displayed in a textbox with the source set to the
expression "=Sum([Hours]). This works fine on the one report which displays
the decimals correctly in the report footer, but won't work at all on the
other report where the total should read 266.85 hours but instead will only
display 266 hours.

Then I used Allen Browne's Page Total code and discovered that the problem
is caused by the total hours for the first page which are displayed
incorrectly (adds up to 10 whereas it should be 10.83). The total hours all
display correctly in the group footers but won't do so in the report footer.
All Totals text boxes properties are set to General number with 2 decimal
places - as is the data in the source query and table.

Why does one report work fine and the other not? I've even completely
rebuilt a new report from the one that works, but to no avail - still gives
the wrong total. Can anyone help? Thanks

Allen Browne's code that I used was as follows:

Option Compare Database
Dim HoursTotal As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then HoursTotal = HoursTotal + Nz(Me.Hours, 0)
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageTotal = HoursTotal
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
HoursTotal = 0
End Sub

That code that you're using isn't supposed to do what you
say you want. I suspect that there may be more to using
that code than just copying it, because there are several
caveats to using an event procedure to calculate a total.
In almost all cases that code can not reliably calculate a
correct total.

Using the Sum function is the right way to do this if Hours
is a field in the report's recordsource table/query.
(Otherwise, you can use a RunningSum text box to add up the
total.)

The first thing I would suspect is that the Hours is somehow
being converted to an integer. To check this, try setting
the Hours text box in the detail section and all the group
and report footer text boxes to use the format 0.000000000
so you can see the whole value.

The next thing to check is the actual, not the formatted,
values in the recordsource table/query. Applying a format
to the table and query just obscurs the values used in the
calculations.

 

[Geoff]

Thanks Marshall - it works fine now using CDbl([Hours]) when I set the format
to 0 and Decimals to 2.

First, you should not try to round, convert or otherwise
mess with the value in the field until you are ready to
display it on a form or report. Using CCur will truncate
the value to four decimal places, which may introdudce small
errors that could accumulate to something significant.

The big conceptual problen is that your use of #0.00# is
forcing a datetime value of 0, which is midnight. If you
want 0 hours, then just write a 0. Don't be confused by the
fact that you are trying to express an elapsed time in hours
with a date/time value. The hours field is NOT a date/time,
it is no different than saying 8.33 feet of material. Since
you are importing the value as a double that is a fraction
of a day, multiplying by 24 is the right way to scale the
number to hours. After that, don't do anything other than
add the values together.

Note that saying that the table field is a number type just
says it's not text or date. It is the Size of the field
that determines what kind of number you are using (Integer,
Long, Double, etc). Make sure your field is a Double.
--
Marsh
MVP [MS Access]

I've tried everything you suggested to no avail, but I have identified that
the root cause of the problem is in the table.

What I do is consolidate the data from a number of Excel time-sheets into a
single workbook. In Excel the Hours column is expressed as 08:20:00. I set
the format for this column to General, multiply by 24 and then use paste
special which converts the value in the applicable cell in the Hours coulmn
to 8.333333333.

I then import the Excel spreadsheet into a table called Import. The then
filter the data in the Import table (sort columns etc) and run a Make Table
query based on the Import Table. The problem is that in both the import table
and the Query table, while the imported value displays correctly as
8.333333333, and the Hours field is set to a Number data type, I'm sure
Access still thinks it is a time data type and therefore can't Total
correctly when using the =Sum([Hours]) function on the report.

My reason for this thinking is that when I try to insert the expression
CCur(#0.00#) for the Hours field in the Make Table query by using the
Expression Builder, Access automatically shows the Criteria value in the
design view of the query to be CCur(#00:00:00#) and not CCur(#0.00#) as
entered. (I'm using the CCur conversion instead of the CDbl conversion to try
and restrict the value to 2 decimals).

Any thoughts on how to build an expression in the Make Table query that will
somehow convert the 8.333333333 time value (although shown as a Number data
type) to a numeric or currency value that I can use in adding up my total
Hours correctly?

Geoff wrote:

I am trying to print the total number of hours billed to a client in a
report. Initially this was displayed in a textbox with the source set to the
expression "=Sum([Hours]). This works fine on the one report which displays
the decimals correctly in the report footer, but won't work at all on the
other report where the total should read 266.85 hours but instead will only
display 266 hours.

Then I used Allen Browne's Page Total code and discovered that the problem
is caused by the total hours for the first page which are displayed
incorrectly (adds up to 10 whereas it should be 10.83). The total hours all
display correctly in the group footers but won't do so in the report footer.
All Totals text boxes properties are set to General number with 2 decimal
places - as is the data in the source query and table.

Why does one report work fine and the other not? I've even completely
rebuilt a new report from the one that works, but to no avail - still gives
the wrong total. Can anyone help? Thanks

Allen Browne's code that I used was as follows:

Option Compare Database
Dim HoursTotal As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then HoursTotal = HoursTotal + Nz(Me.Hours, 0)
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageTotal = HoursTotal
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
HoursTotal = 0
End Sub


That code that you're using isn't supposed to do what you
say you want. I suspect that there may be more to using
that code than just copying it, because there are several
caveats to using an event procedure to calculate a total.
In almost all cases that code can not reliably calculate a
correct total.

Using the Sum function is the right way to do this if Hours
is a field in the report's recordsource table/query.
(Otherwise, you can use a RunningSum text box to add up the
total.)

The first thing I would suspect is that the Hours is somehow
being converted to an integer. To check this, try setting
the Hours text box in the detail section and all the group
and report footer text boxes to use the format 0.000000000
so you can see the whole value.

The next thing to check is the actual, not the formatted,
values in the recordsource table/query. Applying a format
to the table and query just obscurs the values used in the
calculations.