[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???
dcomAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
KMANConnect With a Mentor Commented:
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
 
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.