Date ranges

[Steve]

Hi,

I am after some help with the following problem. We run a query each week
which contains 4 main fields which are Start Date, Start Time, End Date and
End Time. We basically want to capture the sum of the difference in the dates
and times for each week. For example:

Person A: Goes away on 02/12/08 @ 10am and returns 10/12/08 @ 3pm.

Ideally we want to capture the hours for each person from when they went
away to the end of the current week so 02/12 was a Tues and needs to be
captured til the end of the Saturday that same week (06/12). When we come to
run the next weeks query it will capture the remaining hours from 07/12 to
10/12 at 3pm.

Can anyone help me please?

 

[John W. Vinson]

Hi,

I am after some help with the following problem. We run a query each week
which contains 4 main fields which are Start Date, Start Time, End Date and
End Time. We basically want to capture the sum of the difference in the dates
and times for each week. For example:

Person A: Goes away on 02/12/08 @ 10am and returns 10/12/08 @ 3pm.

Ideally we want to capture the hours for each person from when they went
away to the end of the current week so 02/12 was a Tues and needs to be
captured til the end of the Saturday that same week (06/12). When we come to
run the next weeks query it will capture the remaining hours from 07/12 to
10/12 at 3pm.

Can anyone help me please?

A better design would be to use a single date/time field for both the start
and end. An Access Date/Time value is stored as a double float count of days
and fractions of a day since midnight, December 30, 1899; e.g. a time value of
noon is stored as 0.5, corresponding to #12/30/1899 12:00:00#. A pure date
value is stored with a time component of midnight. So rather than one field
containing #6/3/2009 00:00:00# and another field containing
#12/30/1899 11:30:15# you would do better to have just one field containing
#6/3/2009 11:30:15#!

That said... create a Query based on your table. Include a calculated field:

Hours: DateDiff("n", [Start Date] + [Start Time], [End Date] + [End Time])/60.

to calculate the time worked in integer minutes and divide by 60 to get hours
and fractions of an hour. You can use NZ() to get the end of the week, but
it's not clear just how you want this to work.

 

[Steve]

Ideally what we wanted was as follows:
Start Date Start Time End Date End Time
Hrs for week
Person A 02/12/2008 09:00 06/12/08 23:59 111
Person B 03/12/2008 01:00 05/12/08 18:00 65
Person C 06/12/2008 15:00 06/12/08 23:59 9

Basically as you can see the hours are calculated to the end of the week as
the return date is unknown to us at the time so get calculates to the end of
the week(Sat 06/12/08 23:59). The next week would capture:

Person A 07/12/2008 00:00 10/12/08 15:00 15
Person C 07/12/2008 00:00 12/12/08 09:00 129

Hi,

I am after some help with the following problem. We run a query each week
which contains 4 main fields which are Start Date, Start Time, End Date and
End Time. We basically want to capture the sum of the difference in the dates
and times for each week. For example:

Person A: Goes away on 02/12/08 @ 10am and returns 10/12/08 @ 3pm.

Ideally we want to capture the hours for each person from when they went
away to the end of the current week so 02/12 was a Tues and needs to be
captured til the end of the Saturday that same week (06/12). When we come to
run the next weeks query it will capture the remaining hours from 07/12 to
10/12 at 3pm.

Can anyone help me please?

A better design would be to use a single date/time field for both the start
and end. An Access Date/Time value is stored as a double float count of days
and fractions of a day since midnight, December 30, 1899; e.g. a time value of
noon is stored as 0.5, corresponding to #12/30/1899 12:00:00#. A pure date
value is stored with a time component of midnight. So rather than one field
containing #6/3/2009 00:00:00# and another field containing
#12/30/1899 11:30:15# you would do better to have just one field containing
#6/3/2009 11:30:15#!

That said... create a Query based on your table. Include a calculated field:

Hours: DateDiff("n", [Start Date] + [Start Time], [End Date] + [End Time])/60.

to calculate the time worked in integer minutes and divide by 60 to get hours
and fractions of an hour. You can use NZ() to get the end of the week, but
it's not clear just how you want this to work.

 

[Piet Linden]

Ideally what we wanted was as follows:
                   Start Date        Start Time       End Date    End Time  
Hrs for week
Person A      02/12/2008       09:00             06/12/08    23:59         111
Person B      03/12/2008       01:00             05/12/08    18:00         65
Person C      06/12/2008       15:00             06/12/08    23:59         9

Basically as you can see the hours are calculated to the end of the week as
the return date is unknown to us at the time so get calculates to the endof
the week(Sat 06/12/08 23:59). The next week would capture:

Person A     07/12/2008       00:00             10/12/08     15:00         15
Person C     07/12/2008       00:00             12/12/08     09:00         129

Do what John said, and then use Format to show only the date portion
in your query in one column and then the time in another. There are a
chunk of date and formatting functions that make this a walk in the
park. I think you're confusing storage and display of data, which can
be VERY different. For example, you can store the date and then
format it as just the day of the week... *significant" difference!