By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

calculate grand total time in continuous forms

P: 59
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,
Nov 14 '06 #1
Share this Question
Share on Google+
17 Replies


Expert 5K+
P: 8,435
=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.
Nov 14 '06 #2

NeoPa
Expert Mod 15k+
P: 31,266
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.
Nov 14 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:00 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:00 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function totalTime(tTime As Double) As String
  3. Dim days As Integer
  4. Dim hours As Double
  5. Dim minutes As Integer
  6.  
  7.   days = Int(tTime)
  8.   Debug.Print days
  9.   hours = 24 * (tTime - days)
  10.   Debug.Print Int(hours)
  11.   minutes = (hours - Int(hours)) * 60
  12.   Debug.Print minutes
  13.  
  14.   totalTime = days & " days " & Int(hours) & " hrs " & minutes & " mins"
  15.  
  16. End Function
  17.  
  18.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub totalWorkTime_AfterUpdate()
  3. Dim ttltime As Double
  4.  
  5.   ' you first have to save the record that was just updated 
  6.   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  7.  
  8.   ' get the total times  
  9.   Me.calcTime = DSum("TimeValue([DateFiled])", "tblFilingDates", "")
  10.   ttltime = Me.calcTime
  11.  
  12.   ' call the function to calculate days, hours and minutes
  13.   Me.txtTotalTime = totalTime(ttltime)
  14.  
  15. End Sub
  16.  
  17.  
You will also need to put in a form load event as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Load()
  3. Dim ttltime As Double
  4.  
  5.   ' get the total times  
  6.   Me.calcTime = DSum("TimeValue([DateFiled])", "tblFilingDates", "")
  7.   ttltime = Me.calcTime
  8.  
  9.   ' call the function to calculate days, hours and minutes
  10.   Me.txtTotalTime = totalTime(ttltime)
  11.  
  12. End Sub
  13.  
  14.  
I said it was complicated. The problem is that you cannot calculate times over 24 hours which is why you need the function.
Nov 15 '06 #4

NeoPa
Expert Mod 15k+
P: 31,266
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.
Nov 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,266
I tried this with the Now() function originally but, for reproducibility of results I changed it to Date/time literals which are country independant.
Expand|Select|Wrap|Line Numbers
  1. ?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")
  2. 15/11/2006 12:37:33         16/11/2006 00:32:33
This indicates that Date/Time fields conform to standard arithmetic rules.
Nov 15 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 15 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 15 '06 #8

NeoPa
Expert Mod 15k+
P: 31,266
Mary,

You're right. I see what you mean about the days.
But the time element could be better formatted as :
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 15 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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 :
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 15 '06 #10

P: 59
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.
"worktime" is name for the field and that is stored in form.
I don't know where the field must be stored( in table or in form).
Nov 15 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
"worktime" is name for the field and that is stored in form.
I don't know where the field must be stored( in table or in form).
No it's ok, it just changes the approach slightly.

Drop the calcTime field and replace the worktime after update event with the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub worktime_AfterUpdate()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  7.     Set rs = Me.RecordsetClone
  8.     ttltime = 0
  9.  
  10.     rs.MoveFirst
  11.     Do Until rs.EOF
  12.         ttltime = ttltime + TimeValue(rs!worktime)
  13.         rs.MoveNext
  14.     Loop
  15.     Me.txtTotalTime = totalTime(ttltime)
  16.     rs.Close
  17.  
  18. End Sub
  19.  
  20.  
And change to Form load event to a form open event and use the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Open()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.     Set rs = Me.RecordsetClone
  7.     ttltime = 0
  8.  
  9.     rs.MoveFirst
  10.     Do Until rs.EOF
  11.         ttltime = ttltime + TimeValue(rs!worktime)
  12.         rs.MoveNext
  13.     Loop
  14.     Me.txtTotalTime = totalTime(ttltime)
  15.     rs.Close
  16.  
  17. End Sub
  18.  
  19.  
Nov 15 '06 #12

P: 59
No it's ok, it just changes the approach slightly.

Drop the calcTime field and replace the worktime after update event with the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub worktime_AfterUpdate()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  7.     Set rs = Me.RecordsetClone
  8.     ttltime = 0
  9.  
  10.     rs.MoveFirst
  11.     Do Until rs.EOF
  12.         ttltime = ttltime + TimeValue(rs!worktime)
  13.         rs.MoveNext
  14.     Loop
  15.     Me.txtTotalTime = totalTime(ttltime)
  16.     rs.Close
  17.  
  18. End Sub
  19.  
  20.  
And change to Form load event to a form open event and use the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Open()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.     Set rs = Me.RecordsetClone
  7.     ttltime = 0
  8.  
  9.     rs.MoveFirst
  10.     Do Until rs.EOF
  11.         ttltime = ttltime + TimeValue(rs!worktime)
  12.         rs.MoveNext
  13.     Loop
  14.     Me.txtTotalTime = totalTime(ttltime)
  15.     rs.Close
  16.  
  17. End Sub
  18.  
  19.  
Ok, let's go from the beginning.
I have a continuous form with following fields from the table "tblexploatation"
employees;date;beginningtime;endtime;timeout. I made new field "worktime" and use formula FORMAT([endtime]+1-[beginningtime]-nz([timeout];"short time").
Can you tell me which formula or function you use for that the field.
I made function totaltime and two text box in form footer ("totaltime", "grandtotaltime").
In the field "totaltime" I have put it code in after update event as in form open event ,but when I run this return me error in code "ttltime=ttltime+timevalue(rs!worktime)
what is this?
Nov 16 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
continuous form from the table "tblexploatation"

tblexploatation
employees
date
beginningtime
endtime
timeout
worktime (new field in table with date/time data type and Short Time format)

OK

Now on the form for the control for the worktime field make sure the Control Source is set to worktime not FORMAT([endtime]+1-[beginningtime]-nz([timeout];"short time").

I made function totaltime and two text box in form footer ("totaltime", "grandtotaltime").
In the field "totaltime" I have put it code in after update event as in form open event ,but when I run this return me error in code "ttltime=ttltime+timevalue(rs!worktime)
what is this?
You were getting this error because worktime didn't actually exist in the table.

Keep grandtotaltime as a textbox and drop totaltime as a textbox but keep the function (in a separate module). Don't format grandtotaltime to a date/time or number as it will be a string.

Now forget the afterupdate event and set a command button on the form footer. For the example call it cmdCalc.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdCalc_Click()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  7.   Set rs = Me.RecordsetClone
  8.   ttltime = 0
  9.  
  10.   rs.MoveFirst
  11.   Do Until rs.EOF
  12.     rs.Edit
  13.     rs!worktime=(([endtime]+1) - [beginningtime]) - nz([timeout],0)
  14.     rs.Update
  15.     ttltime = ttltime + rs!worktime
  16.     rs.MoveNext
  17.   Loop
  18.  
  19.   Me.grandtotaltime = totalTime(ttltime)
  20.  
  21.   rs.Close
  22.   Set rs=Nothing
  23.  
  24. End Sub
  25.  
  26.  
Nov 17 '06 #14

P: 59
continuous form from the table "tblexploatation"

tblexploatation
employees
date
beginningtime
endtime
timeout
worktime (new field in table with date/time data type and Short Time format)

OK

Now on the form for the control for the worktime field make sure the Control Source is set to worktime not FORMAT([endtime]+1-[beginningtime]-nz([timeout];"short time").



You were getting this error because worktime didn't actually exist in the table.

Keep grandtotaltime as a textbox and drop totaltime as a textbox but keep the function (in a separate module). Don't format grandtotaltime to a date/time or number as it will be a string.

Now forget the afterupdate event and set a command button on the form footer. For the example call it cmdCalc.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdCalc_Click()
  3. Dim ttltime As Double
  4. Dim rs As Recordset
  5.  
  6.   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  7.   Set rs = Me.RecordsetClone
  8.   ttltime = 0
  9.  
  10.   rs.MoveFirst
  11.   Do Until rs.EOF
  12.     rs.Edit
  13.     rs!worktime=(([endtime]+1) - [beginningtime]) - nz([timeout],0)
  14.     rs.Update
  15.     ttltime = ttltime + rs!worktime
  16.     rs.MoveNext
  17.   Loop
  18.  
  19.   Me.grandtotaltime = totalTime(ttltime)
  20.  
  21.   rs.Close
  22.   Set rs=Nothing
  23.  
  24. End Sub
  25.  
  26.  
The field "worktime" is in the form.
Nov 17 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
The field "worktime" is in the form.
You can't sum on a calculated unbound control. You will have to add it to the table.
Nov 17 '06 #16

P: 59
You can't sum on a calculated unbound control. You will have to add it to the table.
I tried with the function "hoursandminutes"

Expand|Select|Wrap|Line Numbers
  1. public function hoursandminutes(interval as variant) as string
  2. dim totalminutes as long, totalseconds as long
  3. dim hours as long,minutes as long, seconds as long
  4.  
  5.   if isnull(interval)=true then exit function
  6.  
  7.   hours=int(CSng(interval*24))
  8.   totalminutes=int(CSng(interval*1440))
  9.   minutes=totalminutes mod 60
  10.   totalseconds=Int(CSng(interval*86400))
  11.   seconds=totalseconds mod 60
  12.  
  13.   if seconds>30 then minutes=minutes+1
  14.   if minutes>59 then hours=hours+1
  15.   hoursandminutes=hours & ":" & format(minutes, "00")
  16.  
  17. end function
  18.  
  19.  
in form detail I made unbound control and put it following formula:
worktime=hoursandminutes([endtime]+1-[beginningtime]-nz([lunch])).
and in footer
totalworktime=hoursandminutes(sum([endtime]+1-[beginningtime]-nz([lunch]))).
Here is one problem! if sum hours exceeding 24 hours function returns me only hours more than 24.
for an example:
28:30 return 4:30
What to do to make the function return full time.
thanks for help me
Nov 18 '06 #17

Expert 5K+
P: 8,435
I tried with the function "hoursandminutes"
...
in form detail I made unbound control and put it following formula:
worktime=hoursandminutes([endtime]+1-[beginningtime]-nz([lunch])).
and in footer
totalworktime=hoursandminutes(sum([endtime]+1-[beginningtime]-nz([lunch]))).
Here is one problem! if sum hours exceeding 24 hours function returns me only hours more than 24.
for an example:
28:30 return 4:30
What to do to make the function return full time.
thanks for help me
Have you checked exactly what value is being received in your function? Your code is quite straightforward and should not "clock over" at 24 hours. But maybe the actual Sum() function (highlighted above) is returning the truncated value. I'd suggest you do a Debug.Print or something on entry to the function, and see what value was received.
Nov 19 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.