Query Challenge with time format 2

[dbero]

I am linking to an Excel spreadsheet to a time formatted field (hh:mm:ss )that indicates how long a person was on the phone. So, 00:8:30 = 0 hours, 8 minutes and 30 seconds.

However, when I link to this table, Access and Excel see this value as 12:08:30 AM, that is as a physical time rather than a duration. So, when I do a Left(time,2) for example, it returns 12.

What I ultimately need to do is import into a db 8.5 minutes of call time for this person. Can anyone help me unformat this time variable into a duration variable?

Thanks much!

 

[PWise]

datepart("s",#12:08:30 AM#)+datepart("s",#12:08:30 AM#)/60

 

[dbero]

I changed your suggestion to include a field value below.

However, 12:22:24 AM is converted to 24.4 Seconds. It should be (22*60)+24
Can you see what i did wrong??


DatePart("s",[total duration])+DatePart("s",[total duration])/60 AS Seconds

 

[dbero]

I think i found it!

DatePart("n",[newtime])*60+DatePart("s",[newtime]) AS Seconds

THanks a bunch

 

[PWise]

sorry about the typo!
it should be datepart("n",#12:08:30 AM#)+datepart("s",#12:08:30 AM#)/60

 

[PHV]

Why not simply this ?
[newtime]*1440

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886