Adding Time

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

I have three fields ([Intime], [Outtime] and [DailyTotal] in a sub form, all
set as short time format.
The dailytotal field calculates the difference between the out time and the
in time fields. This gives me the time worked for that day or period.
Then I have a field [WeeklyTotal] in the footer of the subform that sums the
dailytotal field.
The problem I am having is the weeklytotal field does not give an accurate
number. I do not know how to get it to show time totaling more than 23:59.
 
Pierre

The Access Date/Time data type stores "point in time" data, not "duration"
data.

The FORMAT of the field only controls how it appears, not what is stored.

If your table already has start and end times, why are you also trying to
store a [DailyTotal] (assuming any day can only have a single start and end
time, right?). If you already know start and end, use a query to calculate
the difference.

What you describe sounds more like a spreadsheet than a relational database.

Is there a reason you aren't using a spreadsheet?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Pierre said:
I have three fields ([Intime], [Outtime] and [DailyTotal] in a sub form, all
set as short time format.
The dailytotal field calculates the difference between the out time and the
in time fields. This gives me the time worked for that day or period.
Then I have a field [WeeklyTotal] in the footer of the subform that sums the
dailytotal field.
The problem I am having is the weeklytotal field does not give an accurate
number. I do not know how to get it to show time totaling more than 23:59.


Actually, the weekly total is accurate, you are just not
displaying all of it. Anything over 24 hours is a day, but
since that is probably useless to you, a simple format
change is not sufficient. You need to use a calculation to
get the total in just hours/minutes instead of
days/hours/minutes. I think the total text box expression
would be somethng like:

=Int(Sum(dailytotal) * 24) & Format(Sum(dailytotal), "\:nn")

Be sure to clear the text box's Format property.

OTOH, you did not say how you are calculating the daily
total. If the In/Out time fields do not contain a date
portion, then the calculation will be inaccurate across a
midnight boundary.

What Jeff said about NOT saving the daily total in the table
is wise advice and you should spend some time understanding
why you should not pretend that a database table is just a
big spreadsheet.

The "correct" way to do this kind of thing is to store a
full data/time value in the In/Out time fields. Then use a
query to calculate the daily time in minutes using
DateDiff("n", InTime, OutTime)

Then the form footer text box could then use an expression
to display the weekly total time the way you want:

=Sum(dailytime) \ 60 & Format(Sum(dailytime) Mod 60, "\:00")
 
Back
Top