Hi. First post from a newbie:

I have a database recording cycle rides. I have a table containing data for each ride including start Date/Time and end Date/Time.

Next I have a query based on said table within which I want to generate "Event duration" I have built an expression thus: EventDuration: [tblRides]![RideEventEnd]-[tblRides]![RideEventStart] where the two fields in tblRides are formatted as General Date (i.e. both date and time - necessary because some rides start and finish on different dates) which will give a value e.g. 0.46845

This value looks correct if seen as a proportion of one day of 24 hours.

My problem is that I cannot find a way of displaying this result value as hours:min

Any advice?

Check out the DateDiff() (http://www.techonthenet.com/access/functions/date/datediff.php) Function.

You would need something like;
DateDiff("h",[StartDate],[FinishDate])

Check out the DateDiff() (http://www.techonthenet.com/access/functions/date/datediff.php) Function.

You would need something like;
DateDiff("h",[StartDate],[FinishDate])

Thanks JBB, that's a good start. However, having entered:-
EventDuration: DateDiff('h',[tblRides]![RideEventStart],[tblRides]![RideEventEnd])
- I get a result "11" which is the round number of hours (as the 'h' interval setting implies) whereas what I really need is hrs:mins i.e. "11:25" which is the true answer to this particular record.

Since there is no option in the DateDiff intervals settings list do I assume there's no way of getting this?

Your not going to be able to do it in one hit your will have to do a couple of calculations using the available formats of DateDiff(), the following should do the trick;
=(DateDiff("h",[Start],[end])) & ":" & (((DateDiff("n",[Start],[end]))/60)-(DateDiff("h",[Start],[end])))*60
This will not however work if your start and end times straddle midnight. If you expect this to happen you will need to test for it and implement an alternate calculation.

Your not going to be able to do it in one hit your will have to do a couple of calculations using the available formats of DateDiff(), the following should do the trick;
=(DateDiff("h",[Start],[end])) & ":" & (((DateDiff("n",[Start],[end]))/60)-(DateDiff("h",[Start],[end])))*60
This will not however work if your start and end times straddle midnight. If you expect this to happen you will need to test for it and implement an alternate calculation.

Champion -we're almost there! This code works OK but as you said, when the period straddles midnight, it goes a bit pear-shaped. For example, if I have a start early Saturday morning and a finish late Sunday evening I get e.g. 40.29999999983 - the true answer is 40hrs 3 mins, so this is correct, just not formatted as I'd like i.e 40:03

I've tried importing the result into a Report, and tried "hh:mm" (which displays as Short Time) in the Format option in the Properties list but the data value still appears as 40.29999999983

Might there be a way of adding formatting to the code in the query?

If you are storing your time as General Date (20/02/2010 11:45:00 AM) then the following will deal with times that straddle midnight, if you have times that cross a number of days, you will need a different formula, which I will leave for you to develop (shouldn't be too hard though).

=IIf((((DateDiff("n",[Start],[end]))/60)-(DateDiff("h",[Start],[end])))*60<0,(DateDiff("h",[Start],[end])) & ":" & 60+(((DateDiff("n",[Start],[end]))/60)-(DateDiff("h",[Start],[end])))*60,(DateDiff("h",[Start],[end])) & ":" & (((DateDiff("n",[Start],[end]))/60)-(DateDiff("h",[Start],[end])))*60)

Digi,

How about:

CStr(DateDiff("h",[Start],[end])) & ":" & CStr(DateDiff("n",[Start],[end]) Mod 60)

Wayne