#Error when applying DatePart function to a datetime field

[galexyus]

I have an Access database for which I need to write reporting software. One of the tables has datetime columns such as RingTime, HoldTime, TalkTime, which are supposed to contain the time each portion of a phone call took. I need to be able to do some calculations based on these columns. The problem is when I run a query, let's say:

SELECT TalkTime, DatePart('s', TalkTime) AS TalkTimeSec
FROM Connect

some of the rows return #Error in the TalkTimeSec column, while others return the correct answer. The corresponging TalkTime values, for which I get @Error, are 0:00:00, but not all 0:00:00 values produce #Error. An observation: when I am in datasheet view and I click in a cell containing 0:00:00 that doesn't produce #Error, the value doesn't change, but when I click in the cell that produces #Error, it changes from 0:00:00 to 12:00:00 AM.

If anyone has any idea, what the problem could be, please help. I spent a whole day trying to find a logical explanation and I wasn't successful. If you need to see a sample of the data, I can email you a portion of the table.

[PEB]

Hi man,

U can try to correct the problem like this:

SELECT TalkTime, IIF(TalkTime=0,0,DatePart('s', TalkTime))  AS TalkTimeSec
FROM Connect

Take care

:)

I have an Access database for which I need to write reporting software. One of the tables has datetime columns such as RingTime, HoldTime, TalkTime, which are supposed to contain the time each portion of a phone call took. I need to be able to do some calculations based on these columns. The problem is when I run a query, let's say:

SELECT TalkTime, DatePart('s', TalkTime) AS TalkTimeSec
FROM Connect

some of the rows return #Error in the TalkTimeSec column, while others return the correct answer. The corresponging TalkTime values, for which I get @Error, are 0:00:00, but not all 0:00:00 values produce #Error. An observation: when I am in datasheet view and I click in a cell containing 0:00:00 that doesn't produce #Error, the value doesn't change, but when I click in the cell that produces #Error, it changes from 0:00:00 to 12:00:00 AM.

If anyone has any idea, what the problem could be, please help. I spent a whole day trying to find a logical explanation and I wasn't successful. If you need to see a sample of the data, I can email you a portion of the table.

[galexyus]

Still doesn't work, the problem is that when I run this query:

SELECT ID, TalkTime
FROM Connect
WHERE TalkTime = 0

the result set doesn't even contain the records that generate #Error.