Timestamp query

[durtywhytboy via AccessMonster.com]

Hello:

I have a form with two date fields (DateModified and DateModified2), each
populated with "=Now()" on the click of buttons that trigger other events and
are formatted as General Date (m:d:yyyy h:mm:ss)

Then, I have a field in a query in which I want to return the time elapsed
between the two events in the format of days, hours, minutes. I have tried
the following:

TimeDiff: DateDiff("d:h:n", [DateModified],[DateModified2]) where
DateModified = 3/18/2008 4:26:33 PM and DateModified2 = 3/19/2008 5:58:21 AM.
The correct result in the format I want would be 0:13:31: (0 days, 13 hr, 31
min). It returns "#Error"

I also tried TimeDiff: Format([DateModified2]-[DateModified], "d:h:n") and it
returns "30:13:31". Thats closer to what I want but can't figure out why it
returns 30 days instead of 0.

Ultimately, I also want to knock out the inactivity overnight from 6:30 PM to
5:30 AM, which I have no idea how to accomplish, but one problem at a time.

Thanks for any reply.

 

[John Spencer]

The DateDiff function only return ONE time increment. You can return days,
hours, or minutes - not all three.

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[durtywhytboy via AccessMonster.com]

Thanks John. Exactly the solution I was looking for. Count me as a satisfied
customer!

The DateDiff function only return ONE time increment. You can return days,
hours, or minutes - not all three.

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

Hello:

[quoted text clipped - 26 lines]

Thanks for any reply.