Date/time calculation

K

Kim Doe

Hi. I'm a new Access user.

I'm trying to calculate time difference in minutes between differen
dates. For example, difference in minutes between 6/12/05 15:36 an
6/15/05 00:14.

Currently dates and time are in different columns (separat
variables).

I have seen beautiful code online to solve this problem...but bein
new, was hoping for something simpler.

THANK YOU
 
J

Jeff Boyce

Kim

Since Access has a Date/Time datatype, I'm not sure what you're gaining by
having date and time in different fields/columns.

Once you have your data in Date/Time format (you may be able to use the
CDate() function), you can use the DateDiff() function to calculate the
difference.

Good luck

Jeff Boyce
<Access MVP>
 
K

Kim Doe

Thank you for responding so quickly, Jeff, but when I ran the query, al
the cells read "#error."

I tried this with my two date ([date] and [date2]) variables in genera
format (dd/mm/yyy hh:mm).
DateDiff("n", [date],[date2])

Any suggestions??
 
J

John Spencer (MVP)

Simplest way is to add the Date and Time together.

TheWholeDateTime: [FieldDate] + [FieldTime]

Are the two sets Dates and Times in the same record. Then you should be able to use.

ElapsedTime: DateDiff("n",[FieldDate1] + [FieldTime1],[FieldDate2] + [FieldTime2])
 
J

Jeff Boyce

Kim

What data types are your fields named [date] and [date2]? Not the format,
but the underlying data type?

By the way, the word "date" is a reserved word in Access. If you actually
have a field named "date", you will confuse both Access and yourself.

Try this test:
* create a new empty query
* use the DateDiff() funtion to determine the number of minutes (uses 'n'
interval) between two "hard-coded" dates you enter (e.g., #7/1/2005#,
#7/2/2005#).

What happens when you run this query?

Good luck

Jeff Boyce
<Access MVP>
 
Top