How to total a date field (Short Time) in a crosstab

[ShiftyJaffa]

Hi

I have a database (access 97) of training sessions which each has a date field called "Duration" (this field is in short time format HH:MM). I'm trying to and create a crosstab query which has the locations on the left and a total duration time for each month. If I try to sum the date field in the query I get rubbish, does anyone know a workaround? I'm considering writing a convert to deimal function, but there must be an easier way.

Thanks in advance

Paul

 

[neileg]

Date/type is a poor choice of datatype if you wants to do maths on the values. It would be better to store this as a number of minutes and comvert to hours and minutes for display purposes. Date/time is actually stored as a decimal number with the integer part representing the date based on 31 Dec 1899 as zero, and the fractional part is the time. You can do mathes with this but you can get unexpected results when the totals exceed 24 hours.