Wrong result of Sum (1 Viewer)

[KitaYama]

I have an unbound textbox in the footer of a form to show the sum of a calculated field.
In some cases, the result shown in this textbox is not correct.
I'm trying to understand why the result is not correct (or if my approach is wrong, why the result in some cases is correct).

---------

Control source of the textbox in footer is :
=Nz(Sum([ProcessTime]),0)

A sample database to see the situation is attached.
When you open the database, the form is filtered to show a correct result.
If you select 102 (Adtad） from the combo box above page, the result is not correct.

Thanks for any kind of advice.

 

[The_Doc_Man]

You got caught by a little-appreciated fact: Windows time formatting will not generate a formatted time that exceeds 1 day. I'm assuming you wanted to see a total on the order of 30:30:24 or 29:30:24, but that just can't happen directly.

In your left-hand column, the numbers add up to slightly under 18 hours and the total reflects that fact correctly. (You knew this.)

In the right-hand column, the minutes and seconds are correct, but the hours are one day (24 hours) less than the correct total.

This is because Access doesn't have a native format that gives you hours, minutes, and seconds for intervals greater than 24 hours. You can roll your own formatting function, perhaps. I don't have a copy of one handy at the moment, but here's what you need to do. However you are computing those times, keep on adding them. When you have the sum, present it to a function as a date/time UNFORMATTED number. That is because you are going to do the formatting by hand.

The function can multiply the input time by 86400 (the number of seconds in a day). Then convert that number to LONG integer. This will work as long as your total doesn't exceed several hundred days. Use the MOD function to divide the LONG number by 60 to get a remainder as seconds (set that aside), and then integer divide that same LONG number by 60 to form the 1st intermediate. You now repeat the MOD 60 operation to get minutes (set that aside) and then integer divide your 1st intermediate number by 60 again. That number is the number of hours. You now have a number of hours, a separate number of minutes, and a separate number of seconds. You function can then form the minutes and seconds with leading zeros (if needed) and the integer number of hours of its natural length. Return that as a string. You can then use this function in the same way you would use the standard FORMAT( value, template) function.

 

[KitaYama]

You got caught by a little-appreciated fact: Windows time formatting will not generate a formatted time that exceeds 1 day. I'm assuming you wanted to see a total on the order of 30:30:24 or 29:30:24, but that just can't happen directly.

In your left-hand column, the numbers add up to slightly under 18 hours and the total reflects that fact correctly. (You knew this.)

In the right-hand column, the minutes and seconds are correct, but the hours are one day (24 hours) less than the correct total.

This is because Access doesn't have a native format that gives you hours, minutes, and seconds for intervals greater than 24 hours. You can roll your own formatting function, perhaps. I don't have a copy of one handy at the moment, but here's what you need to do. However you are computing those times, keep on adding them. When you have the sum, present it to a function as a date/time UNFORMATTED number. That is because you are going to do the formatting by hand.

The function can multiply the input time by 86400 (the number of seconds in a day). Then convert that number to LONG integer. This will work as long as your total doesn't exceed several hundred days. Use the MOD function to divide the LONG number by 60 to get a remainder as seconds (set that aside), and then integer divide that same LONG number by 60 to form the 1st intermediate. You now repeat the MOD 60 operation to get minutes (set that aside) and then integer divide your 1st intermediate number by 60 again. That number is the number of hours. You now have a number of hours, a separate number of minutes, and a separate number of seconds. You function can then form the minutes and seconds with leading zeros (if needed) and the integer number of hours of its natural length. Return that as a string. You can then use this function in the same way you would use the standard FORMAT( value, template) function.

@The_Doc_Man good catch. Never thought of that.

Before I go to the function solution, may I ask two questions:

1. The current format of the total text box is hh:nn:ss Is there any way to show the day too?
   In above example : 1 07:30:24
2. I added a new textbox to the footer and Named it tTotal
   then in the current event of the form I added the following :
   

   Dim Total AsSingle
   Dim NumberOfDays As Integer
   Dim R As Single
   
   Total = Me.TotalProcessTime
   NumberOfDays = Int(Total)
   R = Total - NumberOfDays
   
   Me.Ttotal = NumberOfDays & " " & Format(R, "hh:nn:ss")

   
   This one shows the correct result for now. Will I have any issues in long run?

Thanks for taking your time and look into this.
And happy holidays.

 

[The_Doc_Man]

@The_Doc_Man good catch. Never thought of that.

Before I go to the function solution, may I ask two questions:

1. The current format of the total text box is hh:nn:ss Is there any way to show the day too?
   In above example : 1 07:30:24
2. I added a new textbox to the footer and Named it tTotal
   then in the current event of the form I added the following :
   

   Dim Total As Variant
   Dim NumberOfDays As Integer
   Dim R As Single
   
   Total = Me.TotalProcessTime
   NumberOfDays = Int(Total)
   R = Total - NumberOfDays
   
   Me.Ttotal = NumberOfDays & " " & Format(R, "hh:nn:ss")

   
   This one shows the correct result for now. Will I have any issues in long run?

Thanks for taking your time and look into this.
And happy holidays.

This formatting decision actually makes it trivially easy.

Your approach in #2 is exactly the right way to handle that, you just have a couple of data type choices that I would say are, not incorrect, but not optimal. I wouldn't use VARIANT for something that is known to be a DATE variable and I wouldn't use a SINGLE for that remaindering process because it might be off a little at high hour counts i.e. near the end of the day. (Only a little.)

Use DOUBLE for your R and DATE for your TOTAL. Then when formatting R you can convert that to CDATE(R) as the time input for your format - though I'm not going to swear that you even need to do that conversion. That's because DATE is a TYPECAST of DOUBLE and I think the FORMAT function won't care.

Hope your holidays are enjoyable and peaceful. (Unless, of course, you LIKE chaos.)

 

[KitaYama]

Doc, million thanks.
Your suggestions are in place.
You're a star.

I really appreciate your help.

 

[ebs17]

Format for more than 24 hours
This function overrides the VBA.Format function.

But always note: The format function generates a string as return, not a time or number that can be directly used for mathematical calculations.

 

[CJ_London]

not tested but pretty sure you can just use the format property of the control

d hh:nn:ss

but this will show a zero (day) for times that add up to less than a day

 

[KitaYama]

not tested but pretty sure you can just use the format property of the control

d hh:nn:ss

but this will show a zero (day) for times that add up to less than a day

@CJ_London I tested that, but the result was not what I expected. I tested with dd.
To be sure, I'll test once again. But it won't be until Monday.

Thank you and Happy New year.

 

[Pat Hartman]

Just a slight clarification. The DateTime datatype is a POINT in time, hence the formatting issue. That limits you to 23 hours and 59 minutes and 59 seconds before the day rolls and resets the time to 0. Plus, internally, the DateTime is stored as a double precision number with the integer being the number of days since Dec 30, 1899 (Jet/ACE) and the decimal being the elapsed time since midnight. So, 2.75 = Jan 1, 1900 6:00 PM. Two days after 12/30/1899 and 3/4 of the time through the day.

Elapsed time is a different animal. Your solution seems to work so I won't go further. To compound the problem of calculating elapsed time, days/hours/minutes/seconds are not base 10, or even the same base which makes the calculations more complicated.

 

[The_Doc_Man]

To compound the problem of calculating elapsed time, days/hours/minutes/seconds are not base 10, or even the same base which makes the calculations more complicated.

True enough, Pat, but at least the "hidden" DOUBLE datatype that underlies the TYPECAST data type called DATE has enough invisible decimal places that the rounding of time intervals down to seconds is on fairly solid ground. In fact, theoretically a DOUBLE has enough bits so that you could do milliseconds - except that Access doesn't support that unit directly and the clock that feeds time of day isn't precise to the millisecond. (It is accurate to 1/60th of a second.)

 

[KitaYama]

@CJ_London I tested again. dd hh:nn:ss format for the textbox doesn't show the correct result

 

[KitaYama]

I still need some assistance here.
I've put the function I mentioned in #3 in Current event of the form, but still the text box shows 0.
I have to move to another record to re-run the function and then I have the correct value in the textbox.
I have also tried onLoad event, the result is the same.

Which event should I use for this function?
I've updated the database in #1 if you need some tests.

Thanks and happy new year.

 

[Pat Hartman]

Take a look at the example in this database:

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk