Excel ODBC query oddity
Arvi Laanemets
I have some Excel workbook SQLTest with a table on Sheet1 - p.e. having
columns Key and Times (in
columns A and B).
In column Key are some key values, p.e. "AAA", "BBB", "CCC"
In column Times are some time values, p.e. working hours. (I formated the
column as "[hh]:mm", but this is optional.)
I defined named range XXX - p.e. as Sheet1!$A1:$B10, i.e. it contains both
columns Key and Times - and saved the workbook.
On Sheet2, I created an ODBC query from range XXX like:
SELECT XXX.Key, Sum(XXX.Times) AS Hours
FROM `FilePath\SQLTest`.XXX XXX
WHERE (XXX.Key<>'')
GROUP BY XXX.Key
Now, whenever all time values on Sheet1 are less than 24 hours, the query
works OK. But when I enter any time values which are >=24 hours, for every
such entry the
query adds extra 24 hours to sum of this data croup (Hours column in result
table)!
I tested this on 3 different computers - 2 with Office2000 and one with
Office2007 - with same result. (Analysis Toolpack Add-In was installed and
activated on all those
computers.)
Has someone had same problem before? And is there some remedy for it?
Thanks in advance!
Arvi Laanemets