calculate grand total time in continuous forms

=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.

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.

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.

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.

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

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

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.

I tried this with the Now() function originally but, for reproducibility of results I changed it to Date/time literals which are country independant.
This indicates that Date/Time fields conform to standard arithmetic rules.

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.

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.

Mary,

You're right. I see what you mean about the days.
But the time element could be better formatted as :
...though now I test it out - it is a little kludgy - maybe your way's best after all.

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

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).

"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:

And change to Form load event to a form open event and use the following code:

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?

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.

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.

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.

You can't sum on a calculated unbound control. You will have to add it to the table.

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

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.