Calculating time in minutes - whole minutes v. decimals

J

Judy in Allentown

I wrote an expression in a query as follows:
Expr1: ([TIME OUT]-[TIME IN])*1440

For instance: 11/24/2006 11:00:00PM - 11/24/2006 10:59PM = 1 minute

However the result comes up as 1.00000000325963. How do I change the result
to a whole number in a query?

Thank you
 
K

Klatuu

It is always best to use date/time functions when dealing with date/time data
types. The fraction you are getting represents the seconds. The correct
function should be:
Expr1: DateDiff("n",[TIME IN], [TIME OUT])
 
G

geebee

hi,

Try Format([Expr1],"0")
OR
Try Format(([TIME OUT]-[TIME IN])*1440, "0")

Hope this helps,
geebee
 
K

Ken Sheridan

You could use the Round function to round the number to an integer:

Round((TIME OUT]-[TIME IN])*1440)

I wouldn't be completely confident that you would not find that would give
an incorrect result in some circumstances, however, rounding up or down when
you want the opposite, due to the way Access implements date/time values as a
64 bit floating point number as an offset from 30 December 1899 00:00:00.

Another approach would be to compute the difference in nominal seconds and
use integer division to convert it to minutes:

(([Time In] - [Time Out]) *86400)\60

But the safest way would be to use the DateDiff function:

DateDiff("n", [Time In], [Time Out])

The "n" as the first argument causes it to return the result in minutes. It
uses "n" because "m" is used for months.

Ken Sheridan
Stafford, England
 
Top