By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

#Error when applying DatePart function to a datetime field

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TalkTime, DatePart('s', TalkTime) AS TalkTimeSec
  2. FROM Connect
  3.  
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.
Attached Images
File Type: gif query.GIF (15.1 KB, 271 views)
File Type: gif error.GIF (20.8 KB, 224 views)
File Type: gif noerror.GIF (20.7 KB, 265 views)
Oct 1 '06 #1
Share this Question
Share on Google+
2 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi man,

U can try to correct the problem like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT TalkTime, IIF(TalkTime=0,0,DatePart('s', TalkTime))  AS TalkTimeSec
  2. FROM Connect
  3.  
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TalkTime, DatePart('s', TalkTime) AS TalkTimeSec
  2. FROM Connect
  3.  
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.
Oct 1 '06 #2

P: 15
Still doesn't work, the problem is that when I run this query:
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, TalkTime
  2. FROM Connect
  3. WHERE TalkTime = 0
  4.  
the result set doesn't even contain the records that generate #Error.
Oct 1 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.