Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1351
  • Last Modified:

[Oracle][ODBC Oracle Driver]Invalid date, time or timestamp value. (#0)

I use VB code in Access97 to connect to an Oracle Database.  Some tables in the database have Date/Time fields.  Retrieving any and all data in the tables works fine UNLESS one of the Date/Time fields is empty.  That's when I get the following error:

[Oracle][ODBC Oracle Driver]Invalid date, time or timestamp value. (#0)

Getting this error will not let me return any of the data.  The error code breaks and sends me back to the following line of code:

SET recordset = .OpenRecordset()

If my SQL string doesNot try to return the Date/Time field, the code works fine.  By the way, if there is a date in the field (for all records that I'm trying to retrieve...not even one empty) then the code will work appropriately and return even the dates.

Any clues???
0
dcom
Asked:
dcom
  • 6
  • 2
  • 2
1 Solution
 
devthaCommented:
Dim rs as recordset,db as database
set rs = db.openrecordset("Table Name or QueryName")
is the correct way of calling in access...
0
 
devthaCommented:
VB and VBA are little bit of different however VBA is a subset of VB
0
 
dcomAuthor Commented:
Thanks devtha.

You're right. It is VBA.

My code is like you've stated above.  My problem is the error I get because the Date/Time field has no data in it.  If the Date/Time field has data in it, there's no error and no problem.

Maybe I should ask, "Is there a way to return (or read) one field at a time...checking for the field that is blank BEFORE VBA tries to read the entire "recordset" and gives me the error?"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
devthaCommented:
This is when you try to link to the Oracle db thru Access. The database has data with Null dates or this happens when you enter data. Is the date field required?
0
 
KMANCommented:
Here is set of functions that when used on Oracle side or in Access SQL Pass-Through query would handle your "blank" date problem:

to_date(decode(to_char("COLUMN_NAME"),'00-00-0000',Null,to_char("COLUMN_NAME")),'mm-dd-rrrr')

decode checks if date is 'blank' (you'll need to replace the 00-00-0000 string with the proper formatted value for a blank date after conversion to char. If not a 'blank' date, just return the field as is.

HTH, K
0
 
KMANCommented:
If you need update capability of the Oracle table, you may need to create a view on Oracle, as Pass-Through queries are read only.  Otherwise you will have to submit update and inserts from Access to Oracle via PTQueries.  Linked tables won't work with the 'blank' or zero dates.

K
0
 
devthaCommented:
Views are not updateable....
0
 
devthaCommented:
??
0
 
devthaCommented:
??
0
 
dcomAuthor Commented:
Thank you.  I've tried fixing this problem for months.  I forgot about the to_char() function.  Using all of your line of code gave me the error:

"non-numeric where numeric expected"

so I went small...with only the to_char().  Every place the date is empty returns the value "01-01-9999".  I'm not sure why the "numeric" error above but I got to my solution from your comment.

Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now