Q: Hi I am trying to work with a field in a table called CALL DURATION which is a Date/Time field in the format HH:MM:SS. I am trying to work out and then add up the total seconds;
I have put the following in a query field;
total seconds: (Val(Left([CALL DURATION],2)*3600)+Val(Mid([CALL DURATION],4,2)*60)+Val(Right([CALL DURATION],2)))
I would like to 'Sum' the total seconds but every time I try I keep getting the 'Data Type Mismatch..'error message. I don't understand as when I run the query (without the Sum on) the 'total seconds' field appears to be a number and not text. Help I'm going mental!!
Thanks in anticipation
Clare


A: Clare... Left, Right, and Mid are TEXT functions. You can't use them on a time/date without converting the time to a text value first… which you probably don't want to do.

If you want to convert hh:nn:ss into seconds, then you should use the TIME AND DATE functions, in this case Hour() Minute() and Second().

Let's say you have a time in the field T.

Set up three new calculated query fields:

H: Hour([T])
M: Minute([T])
S: Second([T])

Now, with a little math you can figure out how many seconds that represents:

TotalSeconds: ([H]*60*60)+([M]*60)+([S])

Piece of cake.