[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1378
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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