sum calculation is not accurate

[David]

Hi, I'm trying to sum two fields (Time In, and Time Out) in a report.
My code in the Control Source is: =Sum([Time Out]-[Time In]). It works
ok unless the records get to be around a hundred or more, then the
total becomes much smaller than it should be. Is there a reason it
works on a small amount of records, but not a large amount? Thanks for
any help you might be able to give me. David

 

[John W. Vinson]

Hi, I'm trying to sum two fields (Time In, and Time Out) in a report.
My code in the Control Source is: =Sum([Time Out]-[Time In]). It works
ok unless the records get to be around a hundred or more, then the
total becomes much smaller than it should be. Is there a reason it
works on a small amount of records, but not a large amount? Thanks for
any help you might be able to give me. David

If this is a Date/Time field, note that such fields are stored as a Double
Float number, a count of days and fractions of a day since midnight, December
30, 1899. As such they work best as points in time, not as durations. Any
duration over 24 hours will "lap over" into December 31, 1899 - e.g. if the
sum of [Time Out]-[Time In] is 25 hours, the result will be #12/31/1899
01:00:00# which will display as 1 hour.

For durations you're better off using the DateDiff() function to calculate the
difference in time as an integer number, of seconds, minutes, hours or other
units; e.g.

DateDiff("n", [Time In], [Time Out])

will be 490 if Time In is #8:00# and Time Out is #16:10#. This integer will
sum correctly, and can be displayed in hh:nn format with an expression like

<expression>\60 & ":" & Format(<expression> MOD 60, "00")
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Jeff Boyce]

David

As John points out, Access Date/Time fields are "points in time", not
durations.

So, if you are taking the difference between two date/time values, take a
look at the DateDiff() function.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[David]

David

As John points out, Access Date/Time fields are "points in time", not
durations.

So, if you are taking the difference between two date/time values, take a
look at the DateDiff() function.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi, I'm trying to sum two fields (Time In, and Time Out) in a report.
My code in the Control Source is: =Sum([Time Out]-[Time In]). It works
ok unless the records get to be around a hundred or more, then the
total becomes much smaller than it should be. Is there a reason it
works on a small amount of records, but not a large amount? Thanks for
any help you might be able to give me. David- Hide quoted text -

- Show quoted text -

Thanks John and Jeff. It works fine on my first calculation. But when
I try to sum it in the name footer (each group is by name)
calculation, I cannot get the individual records of that group to sum.
I am not sure how to sum the DateDiff() function. Thanks, David

 

[John W. Vinson]

Thanks John and Jeff. It works fine on my first calculation. But when
I try to sum it in the name footer (each group is by name)
calculation, I cannot get the individual records of that group to sum.
I am not sure how to sum the DateDiff() function. Thanks, David

We can't see your computer, David. What's the report's Recordsource (post the
SQL)? How are you doing the sum?

I'd suggest calculating the duration in some appropriate unit (seconds,
minutes, hours, days, months, years are your easy choices) as a calculated
field in the query, and sum that value in the report or group footer.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David]

We can't see your computer, David. What's the report's Recordsource (postthe
SQL)? How are you doing the sum?

I'd suggest calculating the duration in some appropriate unit (seconds,
minutes, hours, days, months, years are your easy choices) as a calculated
field in the query, and sum that value in the report or group footer.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Hi John, I put this in the text box control source: =DateDiff("n",
[Time In],[Time Out]), which works fine for each record time in and
time out. Then, in the text box in my footer to sum all of those
minutes, I have tried: =Sum(DateDiff("hh:nn",[Time In],[Time Out])),
and other things, but cannot get a sum yet. I need to sum it for hours
and minutes, but can't find the proper code to cause that to happen on
my report. Hope you can guide me in the right direction. Thanks, David

 

[John W. Vinson]

Hi John, I put this in the text box control source: =DateDiff("n",
[Time In],[Time Out]), which works fine for each record time in and
time out. Then, in the text box in my footer to sum all of those
minutes, I have tried: =Sum(DateDiff("hh:nn",[Time In],[Time Out])),
and other things, but cannot get a sum yet. I need to sum it for hours
and minutes, but can't find the proper code to cause that to happen on
my report. Hope you can guide me in the right direction. Thanks, David

Press Ctrl-G to open the VBA editor, and search Help for information on
DateDiff. "h" is a valid argument, "n" is a valid argument - but "hh:nn" is
not!

I would suggest a different approach. Put a calculated field in the Query upon
which the report is based, rather than in a textbox on the report; the
calculated field would be

Duration: DateDiff("n", [Time In], [Time Out])

to put a number (a long integer) of minutes as a field in the query. You can
sum number fields in a query on a report; you cannot (effectively) sum
date/time values, as you have found, and you certainly cannot sum text
strings.

In the report's detail section you can just bind a textbox to Duration to see
the (integer) minutes, e.g. 489 for 8 hours 9 minutes; or you can use an
expression

=[Duration] \ 60 & Format([Duration] MOD 60, "\:00")

to display it as 8:09. Similarly, in the Name footer you can use

=Sum([Duration]) \ 60 & Format(Sum([Duration]) MOD 60, "\:00")

to add up the minutes and display the sum in hh:nn format.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David]

Hi John, I put this in the text box control source: =DateDiff("n",
[Time In],[Time Out]), which works fine for each record time in and
time out. Then, in the text box in my footer to sum all of those
minutes, I have tried: =Sum(DateDiff("hh:nn",[Time In],[Time Out])),
and other things, but cannot get a sum yet. I need to sum it for hours
and minutes, but can't find the proper code to cause that to happen on
my report. Hope you can guide me in the right direction. Thanks, David

Press Ctrl-G to open the VBA editor, and search Help for information on
DateDiff. "h" is a valid argument, "n" is a valid argument - but "hh:nn" is
not!

I would suggest a different approach. Put a calculated field in the Queryupon
which the report is based, rather than in a textbox on the report; the
calculated field would be

Duration: DateDiff("n", [Time In], [Time Out])

to put a number (a long integer) of minutes as a field in the query. You can
sum number fields in a query on a report; you cannot (effectively) sum
date/time values, as you have found, and you certainly cannot sum text
strings.

In the report's detail section you can just bind a textbox to Duration tosee
the (integer) minutes, e.g. 489 for 8 hours 9 minutes; or you can use an
expression

=[Duration] \ 60 & Format([Duration] MOD 60, "\:00")

to display it as 8:09. Similarly, in the Name footer you can use

=Sum([Duration]) \ 60 & Format(Sum([Duration]) MOD 60, "\:00")

to add up the minutes and display the sum in hh:nn format.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks very much John, I will work on these possibilities. I truly
appreciate your good help! David

 

[David]

Hi John, I put this in the text box control source: =DateDiff("n",
[Time In],[Time Out]), which works fine for each record time in and
time out. Then, in the text box in my footer to sum all of those
minutes, I have tried: =Sum(DateDiff("hh:nn",[Time In],[Time Out])),
and other things, but cannot get a sum yet. I need to sum it for hours
and minutes, but can't find the proper code to cause that to happen on
my report. Hope you can guide me in the right direction. Thanks,David

Press Ctrl-G to open the VBA editor, and search Help for information on
DateDiff. "h" is a valid argument, "n" is a valid argument - but "hh:nn" is
not!

I would suggest a different approach. Put a calculated field in the Queryupon
which the report is based, rather than in a textbox on the report; the
calculated field would be

Duration: DateDiff("n", [Time In], [Time Out])

to put a number (a long integer) of minutes as a field in the query. You can
sum number fields in a query on a report; you cannot (effectively) sum
date/time values, as you have found, and you certainly cannot sum text
strings.

In the report's detail section you can just bind a textbox to Duration tosee
the (integer) minutes, e.g. 489 for 8 hours 9 minutes; or you can use an
expression

=[Duration] \ 60 & Format([Duration] MOD 60, "\:00")

to display it as 8:09. Similarly, in the Name footer you can use

=Sum([Duration]) \ 60 & Format(Sum([Duration]) MOD 60, "\:00")

to add up the minutes and display the sum in hh:nn format.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Hi John, hope you can help me again. I got all of the above working
fine. But I have a problem when they first put in their member ID in.
If they are not in the database yet, or accidently hit a wrong number,
then I need a msg to pop up to inform them of such. I have been trying
to compare what they enter with what is in the table with something
like this:

If [txtMemID] <> [MemberID] Then
MsgBox "Sorry, you are not yet in the Database. Please see a
Fitness Specialist. Thank You.", vbInformation, "Customer ID
Validation:"
End If

The 'txtMemID' is a hidden text box I put on the form to the field
'MemberID'. Thanks to anyone who might be able to help me. David

 

[David]

Hi John, I put this in the text box control source: =DateDiff("n",
[Time In],[Time Out]), which works fine for each record time in and
time out. Then, in the text box in my footer to sum all of those
minutes, I have tried: =Sum(DateDiff("hh:nn",[Time In],[Time Out])),
and other things, but cannot get a sum yet. I need to sum it for hours
and minutes, but can't find the proper code to cause that to happen on
my report. Hope you can guide me in the right direction. Thanks,David

Press Ctrl-G to open the VBA editor, and search Help for information on
DateDiff. "h" is a valid argument, "n" is a valid argument - but "hh:nn" is
not!

I would suggest a different approach. Put a calculated field in the Query upon
which the report is based, rather than in a textbox on the report; the
calculated field would be

Duration: DateDiff("n", [Time In], [Time Out])

to put a number (a long integer) of minutes as a field in the query. You can
sum number fields in a query on a report; you cannot (effectively) sum
date/time values, as you have found, and you certainly cannot sum text
strings.

In the report's detail section you can just bind a textbox to Duration to see
the (integer) minutes, e.g. 489 for 8 hours 9 minutes; or you can use an
expression

=[Duration] \ 60 & Format([Duration] MOD 60, "\:00")

to display it as 8:09. Similarly, in the Name footer you can use

=Sum([Duration]) \ 60 & Format(Sum([Duration]) MOD 60, "\:00")

to add up the minutes and display the sum in hh:nn format.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com

Hi John, hope you can help me again. I got all of the above working
fine. But I have a problem when they first put in their member ID in.
If they are not in the database yet, or accidently hit a wrong number,
then I need a msg to pop up to inform them of such. I have been trying
to compare what they enter with what is in the table with something
like this:

If [txtMemID] <> [MemberID] Then
    MsgBox "Sorry, you are not yet in the Database. Please see a
Fitness Specialist. Thank You.", vbInformation, "Customer ID
Validation:"
End If

The 'txtMemID' is a hidden text box I put on the form to the field
'MemberID'. Thanks to anyone who might be able to help me.David- Hide quoted text -

- Show quoted text -

Hi everyone, I found the code that made it work perfect. It was
DLookup: here is my code:

If DLookup("[MemberID]", "Members", "[MemberID] = " & Me.txtMemberID)
Then
DoCmd.Close acForm, "frmMemActLogEnter"
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
Else
If DLookup("[MemberID]", "Members", "[MemberID] <> " &
Me.txtMemberID) Then
MsgBox "Sorry, you are not yet in the Database. Please see a
Fitness Specialist. Thank You.", vbInformation, "Customer ID
Validation:"
DoCmd.Close acForm, "frmMemActLogEnter"
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
End If
End If
Thanks again for all of your help. David

 

[John W. Vinson]

Hi everyone, I found the code that made it work perfect. It was
DLookup: here is my code:

If DLookup("[MemberID]", "Members", "[MemberID] = " & Me.txtMemberID)
Then
DoCmd.Close acForm, "frmMemActLogEnter"
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
Else
If DLookup("[MemberID]", "Members", "[MemberID] <> " &
Me.txtMemberID) Then
MsgBox "Sorry, you are not yet in the Database. Please see a
Fitness Specialist. Thank You.", vbInformation, "Customer ID
Validation:"
DoCmd.Close acForm, "frmMemActLogEnter"
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
End If
End If
Thanks again for all of your help. David

That will work... but even better would be to give them a Combo Box to
*select* their ID from a list (probably by name which they should know, rather
than their ID which they might not), rather than making them type an
unfamiliar ID and perhaps get it wrong.

For one thing, just typing an ID doesn't protect them (and you!!) from someone
typing a valid ID that isn't theirs. Neither does the combo, but it at least
makes it possible to verify by name.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David]

Hi everyone, I found the code that made it work perfect. It was
DLookup: here is my code:

If DLookup("[MemberID]", "Members", "[MemberID] = " & Me.txtMemberID)
Then
       DoCmd.Close acForm, "frmMemActLogEnter"
       DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
   Else
   If DLookup("[MemberID]", "Members", "[MemberID] <> " &
Me.txtMemberID) Then
       MsgBox "Sorry, you are not yet in the Database. Please see a
Fitness Specialist. Thank You.", vbInformation, "Customer ID
Validation:"
       DoCmd.Close acForm, "frmMemActLogEnter"
       DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
   End If
   End If
Thanks again for all of your help.David

That will work... but even better would be to give them a Combo Box to
*select* their ID from a list (probably by name which they should know, rather
than their ID which they might not), rather than making them type an
unfamiliar ID and perhaps get it wrong.

For one thing, just typing an ID doesn't protect them (and you!!) from someone
typing a valid ID that isn't theirs. Neither does the combo, but it at least
makes it possible to verify by name.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks John, I really appreciate the help. David