Problem with SQL Summing a Date/Time Field...

[Guest]

I'm connecting to an Access Database with Excel using ADODB and would like to
sum up the
total values for a Date/Time field called Duration.

The SQL I'm using to get this is: "SELECT SUM(Duration) FROM 3_REPORT;"

I am using Excel as my interface and when I run this SQL it returns this
value: "1.6937569511234E+190" regardless of how I format the cell. This is
also the result if I run the SQL inside Access. I was
expecting something like 24:14:01.

If I just cut and paste my data from Access into Excel, then use the excel
function so sum up the total duration, then it gives me what I want. I would
like to do this in the code though.

Any ideas what I'm doing wrong, or how to get around this?

Thanks in advance.

 

[OfficeDev18 via AccessMonster.com]

Try this: SELECT Format(Sum(Duration),"hh:nn:ss") As TotalTime FROM 3_REPORT

Your result is OK. It just needs formatting.

Yes, "nn"; "mm" means "month".

Hope this helps,

Sam

 

[John Vinson]

I'm connecting to an Access Database with Excel using ADODB and would like to
sum up the
total values for a Date/Time field called Duration.

The SQL I'm using to get this is: "SELECT SUM(Duration) FROM 3_REPORT;"

I am using Excel as my interface and when I run this SQL it returns this
value: "1.6937569511234E+190" regardless of how I format the cell. This is
also the result if I run the SQL inside Access. I was
expecting something like 24:14:01.

If I just cut and paste my data from Access into Excel, then use the excel
function so sum up the total duration, then it gives me what I want. I would
like to do this in the code though.

Any ideas what I'm doing wrong, or how to get around this?

Thanks in advance.

Date/Time fields aren't really suitable for durations. They are stored
internally as a Double Float number, a count of days and fractions of
a day (times) since midnight, December 30, 1899. As such, summing
Date/Times will get you some very odd results; in particular, 24:14:01
is stored as approximately 1.0097337962962, and will only display as
#12/31/1899 00:14:01#.

You don't say how you're populating the Duration field, but it sounds
like you may have a 2006 date in there somehow, perhaps with the
formatting displaying only the time portion.

I'd usually store duration data as either Long Integers in the finest
time resolution needed (seconds or minutes), or as a Double Float
count of (say) minutes and fractions of a minute, and then use integer
division and MOD expressions to format as hours, minutes and seconds.
Frex, to display a count of seconds in hh:nn:ss format you can use

[Duration] \ 3600 & Format([Duration] \ 60 MOD 60, ":00") &
Format([Duration] MOD 60, ":00")

John W. Vinson[MVP]