Solved

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

Posted on 2001-08-07
10
1,219 Views
Last Modified: 2008-03-03
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
Comment
Question by:dcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 6

Expert Comment

by:devtha
ID: 6361199
Dim rs as recordset,db as database
set rs = db.openrecordset("Table Name or QueryName")
is the correct way of calling in access...
0
 
LVL 6

Expert Comment

by:devtha
ID: 6361203
VB and VBA are little bit of different however VBA is a subset of VB
0
 

Author Comment

by:dcom
ID: 6361524
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 6

Expert Comment

by:devtha
ID: 6361542
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
 
LVL 5

Accepted Solution

by:
KMAN earned 50 total points
ID: 6363967
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
 
LVL 5

Expert Comment

by:KMAN
ID: 6363980
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
 
LVL 6

Expert Comment

by:devtha
ID: 6363992
Views are not updateable....
0
 
LVL 6

Expert Comment

by:devtha
ID: 6364333
??
0
 
LVL 6

Expert Comment

by:devtha
ID: 6364334
??
0
 

Author Comment

by:dcom
ID: 6364407
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question