Access Rookie - Calculating difference between dates in format mm/dd/yyyy hh:mm

[hockeyroom28]

Hi, I am a longtime Excel user, and am now migrating some work to Access.

I have 2 fields dealing with dates in the format mm/dd/yyyy hh:mm, OPEN and CLOSE

I'd like to get the difference between the two in hours, but I have no idea how or where to do this in Access. I'm assuming it is put into the queries, but it would be easier if I could just put it in the table.

Here is an example
1/1/2008 1:00 1/2/2008 2:00
I want the result to be "25"

Thanks in advance!

 

[rainman89]

try the function datediff

look here
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000162.htm

 

[hockeyroom28]

I tried the following expression in a query

=DateDiff("h",[Open],[Close])

It got the result I wanted, but it only showed as a 2 digit #, leaving out the decimal. Is there anyway to include those?

Thanks again!

 

[rainman89]

try this
=format(DateDiff("h",[Open],[Close]),"0.00")

 

[hockeyroom28]

Thanks for the reply, but this only changes the result from:
42 to 42.00

I think it might be only counting hours, so is there a way to get it for minutes? "m" doesn't work in place of "h". Any ideas?

Thanks!

 

[rainman89]

no it would be "n"

m is for months

 

[hockeyroom28]

Thanks again Rainman. It's close I think...

Here is what I have:
Expr1: (DateDiff("n",[Open],[Close],"0.00")/60)

The original result on Excel was 42.4875 and the result on Access is 42.4833333 and another entry is 4.75 (original was 4.759722222). Any idea?

Thanks!

 

[hockeyroom28]

Bump please...

I'm trying to take the results from Expr1: (DateDiff("n",[Open],[Close],"0.00")/60) in a query and try to have them rounded to 2 decimal places.

 

[Brianwarnock]

Bump please...

I'm trying to take the results from Expr1: (DateDiff("n",[Open],[Close],"0.00")/60) in a query and try to have them rounded to 2 decimal places.

Set the field properties for Expr1 to fixed, Right click in the field select properties from the dropdown select format and then fixed from the dropdown.

Brian

 

[The_Doc_Man]

Do this another way. Here's the theory.

DATE field type in Access is a CAST (a.k.a. TYPECAST) of DOUBLE. A date is really the number of days since the reference date 31-Dec-1899 at midnight. (1-Jan-1900 is day 1 in this scheme.) The time is the fractional part of the day, the date is whole days.

So... try 24 * ( CDbl([Close]) - CDbl([Open]) ) - which should give you hours and fractions to however many decimals you want.

Now... be careful because it ALSO happens that Open and Close are Access keywords. It would not be hard for you to confuse Access by using a keyword at just the wrong moment.