Access 2003

[Guest]

Have Date/Time in & Date/Time out how do I show diff in Hrs in Query

 

[tina]

hours only? or hours:minutes?

for hours only:

ElapsedTime: DateDiff("h", [TimeInField], [TimeOutField])

for hours:minutes:

ElapsedTime: DateDiff("n", [TimeInField], [TimeOutField])/60 & ":" &
DateDiff("h", [TimeInField], [TimeOutField]) Mod 60

(the above goes all on one line.)

hth

 

[Steve Schapel]

Tina,

May I suggest a couple of minor adjustments to your expression:
- integer divisor \ rather than /
- typo "n" instead of "h"
- format so minutes less than 10 will show with leading 0
So...
ElapsedTime: DateDiff("n",[TimeInField],[TimeOutField])\60 & ":" &
Format(DateDiff("n",[TimeInField],[TimeOutField]) Mod 60,"00")

 

[Simvacious Lady]

Hello:

This question is close to what I am trying to accomplish. I am attempting
to create a validation rule: DateDue=DateOut+3
DateIn=DateDue +/- #of days = late/early

This is for a CheckOut/CheckIn Database I am creating

Is this expression possible?

Dee

 

[tina]

much better, Steve!

Tina,

May I suggest a couple of minor adjustments to your expression:
- integer divisor \ rather than /
- typo "n" instead of "h"
- format so minutes less than 10 will show with leading 0
So...
ElapsedTime: DateDiff("n",[TimeInField],[TimeOutField])\60 & ":" &
Format(DateDiff("n",[TimeInField],[TimeOutField]) Mod 60,"00")

--
Steve Schapel, Microsoft Access MVP

hours only? or hours:minutes?

for hours only:

ElapsedTime: DateDiff("h", [TimeInField], [TimeOutField])

for hours:minutes:

ElapsedTime: DateDiff("n", [TimeInField], [TimeOutField])/60 & ":" &
DateDiff("h", [TimeInField], [TimeOutField]) Mod 60

(the above goes all on one line.)

hth

 

[Steve Schapel]

Dee,

I expect you will be able to create a Validation Rule to do what you are
seeking. But, to be honest, you've sort of lost me here, as regards the
details. Maybe you could try to explain the Rule you want to enforce,
using plain English. For example: "The CheckOutDate must be less than 3
days after the CheckInDate".

 

[Simvacious Lady]

Hi Mr. Schapel:

Okay, let's see if I can clarify what I want to accomplish. I am creating
a rental database where I want to:

1. Make the DateDue the CheckOutDate + a certain number of days days.
2. If returned late, then DateDue + whatever number of days returned late
3. If returned early, then DateDue - number of days returned early

Does this make sense to you? Sort of the way Blockbuster does video rentals
or the Library when items are checked out and are returned late. I hope I
explained this in plain English.

Thank You for Responding!

Dee

 

[Steve Schapel]

Dee,

Thanks for the further explanation, which helps.

It seems to me that we are not really talking about a Validation Rule
here. A Validation Rule in the database is for the purpose of ensuring
that the actual data entered complies with certain conditions. It is
often for the purpose of reducing human error in data entry.

Whereas it looks like in your example you are really wanting to do a
calculation to determine whether the return of the rental is late. Am I
right? If so, is the "certain number of days" always the same for every
rental, or does it vary according to item or whatever? You should not
have a Due Date field in your table in the database... this is a derived
value based on the CheckOutDate and the RentalPeriod (certain number of
days). You should also have a ReturnDate or CheckInDate. On the basis
if this data, you will be able to use a query to easily show the status
of all rentals.

 

[Simvacious Lady]

Hi Mrl Schapel:

You are Absolutely correct! Whew! I wasn't sure if I explained it well
enough in plain English.

The "certain number of days" will always remain the same for all rentals.
Okay, I will see if I can create a query to accomplish the result I wish to
achieve. Thank You for All Your Assistance.

Respectfully

Dee

 

[Steve Schapel]

Very good, Dee. Best wishes with your project. Please don't hesitate
to post back here if you need further help.