I'm pulling data from an SQL database into Access2007 through a linked table. There are two time fields: [STOP] and [ACK], both in the form HH:MM:SS, and I have a field called DOWNTIME_SEC that calculates the difference between those times, in seconds, with the formula
CLng(IIf([STOP]="ERR" Or [ACK]="ERR","",Round((CDate([ACK])-CDate([STOP]))*60*60*24,0)))
which gives me accurate results.
My report is grouped by date, and the date footer contains the formula =Sum([DOWNTIME_SEC]), but that causes the error "Data type mismatch in criteria expression."
I've tried CDbl, Val, etc, but to no avail. CInt gives me an overflow error. I've even created another field called [OTHER] and tried converting [DOWNTIME_SEC] to various formats, but I end up with the same problem. I've created another table and typed the data in manually (using the same formula for [DOWNTIME_SEC]), produced a report from it, and THAT works, but I can't do that hundreds of times a day every day. I would like to do this with only regular queries and without resorting to VBA, Macros, or any other 'fancy' stuff. Any suggestions?