Help With Date Calculations

[Guest]

I am a relative Newbee at Access, but could really use help of this.
I am looking for a way to calculate "Hours off" since the end of the last
shift worked. My database is set up so that we enter "Date In, Time In, Date
Out, Time Out". My problem is that I need to make the calculation based on
the record from the prior date. Any suggestions on how to do this.

Thanks,

 

[John Vinson]

On Fri, 28 Jul 2006 11:27:02 -0700, Robert A Cina <Robert A

I am a relative Newbee at Access, but could really use help of this.
I am looking for a way to calculate "Hours off" since the end of the last
shift worked. My database is set up so that we enter "Date In, Time In, Date
Out, Time Out". My problem is that I need to make the calculation based on
the record from the prior date. Any suggestions on how to do this.

Thanks,

Since you don't say what the calculation involves, it's more than a
bit difficult to tell you how to do it!

Note that Access stores date/time values as Double Float numbers, a
count of days and fractions of a day (times) since midnight, December
30, 1899. Therefore any "just time" value is actually a Date value on
that long-ago day. It's often easier to use just one field for both
date and time. For example, if you have two records in a table, one
with a DateTimeOut field and the other with DateTimeIn, you can use
the DateDiff() function to calculate the duration (in minutes) between
them; one (of several) ways would be

TimeOff: DateDiff("n", DMax("[DateTimeOut]", "[tablename]",
"[DateTimeOut] < #" & [DateTimeIn] & "#"), [DateTimeIn])

This uses the DMax() function to locate the most recent prior
DateTimeOut value in the table prior to this record's DateTimeIn.

With two fields, it's more complex since you must combine them prior
to doing the calculation.

John W. Vinson[MVP]