Jeanette Cunningham said:Calculate the sum of the field for hours worked.
Assuming your form is filtered to show just the days from the current week,
use something like =Sum([HrsWorked]) in an unbound textbox in the form's
header or footer.
Where HrsWorked is the name of the field for hours worked in the table or
query behind the form.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Northern Dave said:I have a form that gives the total hours worked each day, I would like to
add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week
KenSheridan via AccessMonster.com said:I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether you
are using a date/time data type as a basis for the HoursWorked? The
date/time data type stores values representing a point in time not durations
of time. The values are actually stored as a 64 bit floating point number as
an offset from 31 December 1899 00:00:00.
Time sheet applications usually record the start and end times of each
continuous work period, e.g. day or half-day. If you subtract the start time
from the end time you get a value which when formatted as a time does in fact
show the time duration, but only if less than 24 hours. Above that the
result formatted as a time would be the time less 24 hours (or multiples of
24 if the duration spans several days). If the individual differences are
each less than 24 hours, summing them and formatting the result as time will
again only give the result less 24 hours or multiples thereof.
There are a number of ways around this. You could for instance return the
difference in minutes between each start and end time using the DateDiff
function and then convert this into hours, e.g. 7 hours 30 minutes would be
represented as 7.5. These values can then easily be summed to give the total
hours worked per week.
Another way is to do the whole calculation on the basis of the underlying
values and convert that to a string showing the hours and minutes. The
following function does this:
Public Function TimeElapsed(dblTotalTime As Double) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")
TimeElapsed = lngHours & strMinutesSeconds
End Function
In query for instance you could have a computed column which calls the
function:
TotalHours: TimeElapsed(Sum([EndTime] – [StartTime]))
or you could do similarly with a computed control on a form.
Ken Sheridan
Stafford, England
Northern said:I have a form that gives the total hours worked each day, I would like to add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week
KenSheridan via AccessMonster.com said:If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a string
expression, so you can then use the values in arithmetical expressions:
=Round(DateDiff("n",[friin],[friout])/60,2)
Does the fact that your fields are prefixed with 'fri' mean that you have
separate columns in the table for the start and end times of each day of the
week? If so you can't sum the values of course you'd have to add the values
of the individual expressions:
Round(DateDiff("n",[moniin],[monout])/60,2)+Round(DateDiff("n",[tuein],
[tueout])/60,2)+ etc
If this is the case it would point to a design flaw. A good design should
have one row per employee/day, with timein and timeout columns. You can then
sum rather than add the values for a week. You could have a separate
workdate column, but the best approach is to include the date in the timein
and timeout values. There is no such thing as time value per se in Access in
fact, only a date/time value. When you enter a time without a date you are
actually entering the time on 30 December 1899, Access's day-zero. You can
this for yourself if you enter the following in the debug (aka immediate)
window:
? Format(#08:30#,"dd mmmm yyyy, hh:nn:ss")
Ken Sheridan
Stafford, England
Northern said:Hello KenSheridan,
You have answered some of my next to be posted question, I now have the
results using a simple formala
=Format(DateDiff("n",[friin],[friout])/60,".00"), this gives me the results
as 08:30-18:00 =9.5 which is aceptable as the accountants only deal in
decimal time for the pay run.
Having got the total daily hours returning in the format 9.5 how would I sum
these daily totals, or alas convert them so they can be totalled. I have
tried alsorts using trail and error, and searched the community for
inspiration but with no look, would be well pleased if you could help.
[quoted text clipped - 52 lines]I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether youall these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week
KenSheridan via AccessMonster.com said:A good starting point would be Crystal's introductory tutorial at:
http://www.accessmvp.com/Strive4Peace/
It does what it says on the tin.
Ken Sheridan
Stafford, England
Northern said:Hello Ken
Yet again your perception is extremely accurate, and yes I do have an in,
out & total for each day Mon-Fri. having spent some time reading the
questions and answers within this very helpful site I understand the concept
of there not being a time calculation but it being the difference between two
date times.
I have a single row in a table updated from a form so that you may pick the
week commencing date and employee name and then input the in/out with a query
calculting the Daily totals, and hopefully a weekly total. The idea is that
another record is added to the table every time an entry is made for an
employee (with the option to edit the data) on a respective week commencing.
I am now going to play with different versions of the table using your
suggestions, but it seems I may need to read some indepth literaure on either
visual Basic and/or programming Access, with your obvious knowledge would you
be kind enough to suggest any literature that you consider very good.
May I take this opportunity to thank you for your time and help with solving
my problem.
[quoted text clipped - 43 lines]If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a stringall these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.