Calculating time

[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?

 

[scubadiver]

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

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

 

[Jeff Boyce]

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]

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

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

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

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?

 

[Annemarie]

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.

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

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?

 

[Jeff Boyce]

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