calculate grand total time in continuous forms

[barkarlo]

I need help to make grand total time in continuous forms.
to calculate total work time I use following formula
=format([timeout]+1-[timein]-nz([lunch]);"short time").
but when I make grand total time in form footer (for all records) my result is error.
My grand total must be in format [hh:mm].
thanks,

[Killer42]

=format([timeout]+1-[timein]-nz([lunch]) ; "short time")

Can you just check something for me? I'm not certain, but I think the semicolon should be a comma.

[NeoPa]

The Format() function returns a string value.
It doesn't matter that the characters in the string look like it may be a number - Access is not fooled.
You can't perform arithmetic of any sort on a string.

Rather than using the Format() function, try using the Format property of the TextBox instead.

[MMcCarthy]

I need help to make grand total time in continuous forms.
to calculate total work time I use following formula
=format([timeout]+1-[timein]-nz([lunch]);"short time").
but when I make grand total time in form footer (for all records) my result is error.
My grand total must be in format [hh:mm].
thanks,

Using this formula given the values below
= Format(([timeout] + 1 - [timein] - nz([lunch],0), "short time").

'if lunch is one hour
= Format((#6:12:0 0 PM# + 1) - (#9:25:00 AM#) - Nz(#1:00:00 AM#, 0), "short time")
Result: 07:47:00

'if lunch is a null value
= Format((#6:12:0 0 PM# + 1) - (#9:25:00 AM#) - Nz(Null, 0), "short time")
Result: 08:47:00

Now to sum in the footer:

This is quite complicated.

Firstly you will need two textboxes in the footer. One to hold the sum of time called calcTime (set visible to "No") and the other to display the result called txtTotalTime. Don't set either of them to anything for the moment. This will have to be done using VBA code.

Next you need to put the following function in a module.

 
Function totalTime(tTime As Double) As String
Dim days As Integer
Dim hours As Double
Dim minutes As Integer
 
  days = Int(tTime)
  Debug.Print days
  hours = 24 * (tTime - days)
  Debug.Print Int(hours)
  minutes = (hours - Int(hours)) * 60
  Debug.Print minutes
 
  totalTime = days & " days " & Int(hours) & " hrs " & minutes & " mins"
 
End Function
 
 

Now finally you will have to create an AfterUpdate function based on the control holding total work time value for each record on the main form.
For the example I'm calling it totalWorkTime as I don't know what you've named it.

 
Private Sub totalWorkTime_AfterUpdate()
Dim ttltime As Double
 
  ' you first have to save the record that was just updated 
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
  ' get the total times  
  Me.calcTime = DSum("TimeValue([DateFiled])", "tblFilingDates", "")
  ttltime = Me.calcTime
 
  ' call the function to calculate days, hours and minutes
  Me.txtTotalTime = totalTime(ttltime)
 
End Sub
 
 

You will also need to put in a form load event as follows:

 
Private Sub Form_Load()
Dim ttltime As Double
 
  ' get the total times  
  Me.calcTime = DSum("TimeValue([DateFiled])", "tblFilingDates", "")
  ttltime = Me.calcTime
 
  ' call the function to calculate days, hours and minutes
  Me.txtTotalTime = totalTime(ttltime)
 
End Sub
 
 

I said it was complicated. The problem is that you cannot calculate times over 24 hours which is why you need the function.

[NeoPa]

Are you sure about this Mary?
Times, being a fraction of a day should work as any other number behind the scenes to my understanding.
Looks like I'll have to run some tests over my lunch ;).
I'll post my findings, but let me know if you think I've not understood something.

[NeoPa]

I tried this with the Now() function originally but, for reproducibility of results I changed it to Date/time literals which are country independant.

?Format(#15 Nov 12:37:33#,"d/m/yyyy hh:nn:ss"), Format(#15 Nov 2006 12:37:33#+#11:55:00#,"d/m/yyyy hh:nn:ss")
15/11/2006 12:37:33         16/11/2006 00:32:33

This indicates that Date/Time fields conform to standard arithmetic rules.

[MMcCarthy]

Are you sure about this Mary?
Times, being a fraction of a day should work as any other number behind the scenes to my understanding.
Looks like I'll have to run some tests over my lunch ;).
I'll post my findings, but let me know if you think I've not understood something.

That's not the problem. The problem is that if as I suspect only the time is being recorded then the date field can't be used in the arithmetic.

Therefore as the total time is being summed over all records in the form this would go over 24 hours and that cannot be handled with a specific format or any of the existing date functions.

The result returns a decimal number which needs the function I've included to break it out into days hours and mins.

[MMcCarthy]

I need help to make grand total time in continuous forms.
to calculate total work time I use following formula
=format([timeout]+1-[timein]-nz([lunch]);"short time").
but when I make grand total time in form footer (for all records) my result is error.
My grand total must be in format [hh:mm].
thanks,

Barkarlo

Just a note regarding this line in the code

Me.calcTime = DSum("TimeValue ([DateFiled])", "tblFilingDates ", "")

The field and table names here are from the test I ran. Change tblFilingDate to your own table name and [DateFiled] to the field in which you are storing the calculated Total_Hours value. If you are not storing that field you will need to for this to work. Otherwise let me know and I'll change the code accordingly.

[NeoPa]

Mary,

You're right. I see what you mean about the days.
But the time element could be better formatted as :

Format(tTime, "h \hr\s n \mi\n\s")

...though now I test it out - it is a little kludgy - maybe your way's best after all.

[MMcCarthy]

Mary,

You're right. I see what you mean about the days.

Don't you know by now I'm always right.

But the time element could be better formatted as :

Format(tTime, "h \hr\s n \mi\n\s")

...though now I test it out - it is a little kludgy - maybe your way's best after all.

Well DAWHHH!!!

Actually, I knew about the problem as I've come up against it before. Experience is a great teacher.

Mary