Solved

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

Posted on 2001-08-07
10
1,163 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 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