How do I calculate elapsed time in a simple query when the starting time is
before 00:00 and the end time after 00:00 ... all I'm getting at the moment
is a negative number?

Use the DateDiff function for the two values in question.
--
Bruce


"Machyde" wrote:

> How do I calculate elapsed time in a simple query when the starting time is
> before 00:00 and the end time after 00:00 ... all I'm getting at the moment
> is a negative number?

A date time is store as a floating point number, but around 0, there is
something different than the way floating point numbers work.

? Format( -0.25, "long date"), Format( -0.25, "long time")
Saturday, 30 December, 1899 06:00:00

? Format( 0.25, "long date"), Format( 0.25, "long time")
Saturday, 30 December, 1899 06:00:00

So, note that both 0.25 and -0.25 return the same point in time! (even if
the value differ, ie, 0.25 <> -0.25 , if considered as floating point
number).

So, if you have to play with intervals (ie, with date_time values when the
integer part is zero or near zero), be sure to use DatePart and DateDiff,
which are aware of that particularity, rather than using plain arithmetic, +
and -, which behave as floating point number behave, NOT the way date and
time implementation behave.


Vanderghast, Access MVP


"Machyde" <(E-Mail Removed)> wrote in message
news:845B96BD-8922-4860-98C9-(E-Mail Removed)...
> How do I calculate elapsed time in a simple query when the starting time
> is
> before 00:00 and the end time after 00:00 ... all I'm getting at the
> moment
> is a negative number?

The following expression can be used to calculate elapsed time when there is
no date component and times may be over two days. For example, 11:49 PM to
1:00 AM

Number of Minutes:
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Number of Seconds:
(DateDiff("s",Starttime,Endtime) + 86400) Mod 86400

If the starting time and ending time fields also contain a date component then
you can use DateDiff without the manipulation of the results returned by the
DateDiff function.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Machyde wrote:
> How do I calculate elapsed time in a simple query when the starting time is
> before 00:00 and the end time after 00:00 ... all I'm getting at the moment
> is a negative number?