Date and Time Calcs in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I inherited a database....I feel the need to start with this because I know
the date and time fields were not set up correctly. Not my fault...lol.

Anyway, tbl_AssocWkHrs has the following fields:

Report Date
Start Date
Start Time
End Date
End Time
Activity Type

I need to be able to calculate the difference between the end date and time
and the start date and time (i.e. how long did it take to work?) The dates
must be included because some of the work will span two different days. The
report date field is used to define which date the shift started.

I know the data should all be in one field, but with over 9000 records it's
too late to change. How can I set up a query that will calculate how long it
took each person to work a load?

I am using Access 97.

Thanks in advance for your help
 
You can combine the 2 fields into one date/time field by typing this into a
fresh column in the Field row in query design:
CVDate([Start Date] + [Start Time])

So the difference in hours-and-fractions-of-an-hour would be:
Hours: CDbl(Nz(DateDiff("n", CVDate([Start Date] + [Start Time]),
CVDate([End Date] + [End Time])) / 60,0))
 
Try something like the following which assumes there are no nulls in the
date/time fields:

SELECT *, DateDiff("n", [Start Date] + [Start Time], [End Date] + [End
Time]) /60 As WrkHours
FROM tbl_AssocWkHrs;
 
Thanks....You guys are awesome!!!!!

Duane Hookom said:
Try something like the following which assumes there are no nulls in the
date/time fields:

SELECT *, DateDiff("n", [Start Date] + [Start Time], [End Date] + [End
Time]) /60 As WrkHours
FROM tbl_AssocWkHrs;

Boz said:
I inherited a database....I feel the need to start with this because I know
the date and time fields were not set up correctly. Not my fault...lol.

Anyway, tbl_AssocWkHrs has the following fields:

Report Date
Start Date
Start Time
End Date
End Time
Activity Type

I need to be able to calculate the difference between the end date and
time
and the start date and time (i.e. how long did it take to work?) The
dates
must be included because some of the work will span two different days.
The
report date field is used to define which date the shift started.

I know the data should all be in one field, but with over 9000 records
it's
too late to change. How can I set up a query that will calculate how long
it
took each person to work a load?

I am using Access 97.

Thanks in advance for your help
Click to expand...
Click to expand...
 
Back
Top