Calculating time

  • Thread starter Thread starter Annemarie
  • Start date Start date
A

Annemarie

I built a time sheet, but I'm having difficulty calculating the fields. My
table name is time_table, my Query for the calculations is time_qry, and my
form name is time_form. I have a time stamp for time in, lunch out, lunch in,
and time out. (day_time_in_Monday, lunch_out_Monday, lunch_in_Monday,
day_time_out_Monday). Here is the code I'm using for field
day_total_hours_Monday; which is going to hold the total time for the day (I
used the expression builder).

day_total_hours_Monday: ( [time_table]![day_time_out_Monday] -
[time_table]![day_time_in_Monday] ) - ( [time_table]![lunch_in_Monday] -
[time_table]![lunch_out_Monday] )

Basically, (time out - time in)-(lunch out - lunch in) = total hours for the
day

This is the result I get: 0.341099537043192
My Time stamps are:
day_time_in_Monday 11:13
lunch_out_Monday 13:24
lunch_in_Monday 13:45
day_time_out_Monday 19:45

My table doesn't save the total times, it stays at 0. Can someone tell me
what I'm doing wrong?
 
Add this (or you can put the format round the whole expression)

Total: format([day_total_hours_Monday], "hhmm")
 
It all starts with the data...

What is the datatype of the field(s) you use (I'm guessing Date/Time)?

Based on your description, it sounds like you might be using one column for
each timestamp for each day of the week. If so, that's a spreadsheet, not a
relational database table!

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Like this?
format(day_total_hours_Monday: ( [time_table]![day_time_out_Monday] -
[time_table]![day_time_in_Monday] ) - ( [time_table]![lunch_in_Monday] -
[time_table]![lunch_out_Monday] ), [day_total_hours_Monday], "hhmm")

scubadiver said:
Add this (or you can put the format round the whole expression)

Total: format([day_total_hours_Monday], "hhmm")

Annemarie said:
I built a time sheet, but I'm having difficulty calculating the fields. My
table name is time_table, my Query for the calculations is time_qry, and my
form name is time_form. I have a time stamp for time in, lunch out, lunch in,
and time out. (day_time_in_Monday, lunch_out_Monday, lunch_in_Monday,
day_time_out_Monday). Here is the code I'm using for field
day_total_hours_Monday; which is going to hold the total time for the day (I
used the expression builder).

day_total_hours_Monday: ( [time_table]![day_time_out_Monday] -
[time_table]![day_time_in_Monday] ) - ( [time_table]![lunch_in_Monday] -
[time_table]![lunch_out_Monday] )

Basically, (time out - time in)-(lunch out - lunch in) = total hours for the
day

This is the result I get: 0.341099537043192
My Time stamps are:
day_time_in_Monday 11:13
lunch_out_Monday 13:24
lunch_in_Monday 13:45
day_time_out_Monday 19:45

My table doesn't save the total times, it stays at 0. Can someone tell me
what I'm doing wrong?
Click to expand...
Click to expand...
 
Yes, Date/Time in short time (13:00)
The total for the day is a number
i have each day on one row, so each week is a new entry
I want to use access rather than excel because i can do so much more with
access.

Jeff Boyce said:
It all starts with the data...

What is the datatype of the field(s) you use (I'm guessing Date/Time)?

Based on your description, it sounds like you might be using one column for
each timestamp for each day of the week. If so, that's a spreadsheet, not a
relational database table!

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Annemarie said:
I built a time sheet, but I'm having difficulty calculating the fields. My
table name is time_table, my Query for the calculations is time_qry, and
my
form name is time_form. I have a time stamp for time in, lunch out, lunch
in,
and time out. (day_time_in_Monday, lunch_out_Monday, lunch_in_Monday,
day_time_out_Monday). Here is the code I'm using for field
day_total_hours_Monday; which is going to hold the total time for the day
(I
used the expression builder).

day_total_hours_Monday: ( [time_table]![day_time_out_Monday] -
[time_table]![day_time_in_Monday] ) - ( [time_table]![lunch_in_Monday] -
[time_table]![lunch_out_Monday] )

Basically, (time out - time in)-(lunch out - lunch in) = total hours for
the
day

This is the result I get: 0.341099537043192
My Time stamps are:
day_time_in_Monday 11:13
lunch_out_Monday 13:24
lunch_in_Monday 13:45
day_time_out_Monday 19:45

My table doesn't save the total times, it stays at 0. Can someone tell me
what I'm doing wrong?
Click to expand...
Click to expand...
 
The Date/Time data type in Access stores, well, a date and a time. So even
if someone checked in just before midnight and checked out the next day, if
you are using the Now() function to put a date/time stamp there, you will
have everything you need.

Next, the Date/Time data type stores a decimal number to represent date
(whole number) and time (decimal fraction). You might want to take a look
at using the DateDiff() function to find the difference.

NOTE: the 'format' you use to display the data does NOTHING to the
underlying value stored, it just changes how it appears.

In a spreadsheet, you'd use one column per day (or per day X activity), as
you've described. This is a spreadsheet, not a relational database table!

The relationally-oriented features/functions Access offers don't work too
well if you try to feed them 'sheet data.

If I've understood your data design (one column per day X activity), you
have to keep adding columns for next day's activities and next week's
activities and ... This is a maintenance nightmare, since you'll need to
change your forms, queries, reports, code, etc.

Instead, a relational table is narrow-but-deep (where the spreadsheet
approach just gets wider and wider).

Please post a description of your table that shows all the field names (or
enough to help make sure I haven't misinterpreted your description). Here's
a commonly-used way of describing a table in these newsgroups:

tblPerson
PersonID
FirstName
LastName
DOB
...

If your table does get wider and wider (to add weekdays X activities),
here's a potential alternate table structure:

tblTimesheet
TimesheetID (primary key)
PersonID (whose time?)
ActivityID (which activity -- check-in, check-out, lunch-out,
lunch-back?)
TimesheetDateTime (?your "timestamp")

Note that this design allows for simple queries to find all the activities
on a particular date for a particular person. Once you have those, you can
do the calculations you mentioned in your earlier post.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top