Solved

MS Access ODBC into Oracle gives error when encountering a null value in Oracle.

Posted on 2006-07-14
16
1,511 Views
Last Modified: 2011-10-03
I am querying an Oracle (8 or 9) Database from MS 2003 front end. I get an error message [Oracle][ODBC] Invalid date/time format. (#0)
I think this is because there are null fields in the Oracle date field. I have tried using a function to test for nulls and assign an arbitrary date (the "null" date fields in Oracle are from the initial data load and I don't need results from that time frame). Nothing seems to work. Please help. I mark this "urgent" because I am under a crushing time deadline to fix this.
0
Comment
Question by:joelmcm
  • 6
  • 4
  • 4
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Is this a passthru query (i.e. run on oracle) or an access query (i.e. run on locally linked tables)

this matters as syntax is different


WIth oracle, u can use NVL  basically substitute a null value with some default value

can u post your sql?

0
 

Author Comment

by:joelmcm
Comment Utility
It is run locally on a table linked to an Oracle db.

SELECT [1ML_RPTOBS_CMPPG].PID, [1ML_RPTOBS_CMPPG].HDID, [1ML_RPTOBS_CMPPG].OBSDATE
FROM 1ML_RPTOBS_CMPPG
WHERE ((([1ML_RPTOBS_CMPPG].HDID)=28));

where 1ML_RPTOBS_CMPPG is the linked table.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
This is a straight forward select, I wonder how u manage to get errors like that

I guess u could try checking for nulls like this


SELECT PID, HDID, IIF(ISNULL(OBSDATE)=True,'No Date',OBSDATE)
FROM 1ML_RPTOBS_CMPPG
WHERE HDID=28


0
 
LVL 5

Expert Comment

by:BPeb
Comment Utility
My guess is that since your using this as a local table then Access is pulling all of the data inculding the null dates you don't care about.  You could create an Oracle view, or Oracle Query that restricts these records and then link to that instead so Access doesn't see them.  If you think might need that data some day then you could use an NVL to force a bogus value like 1/1/1111 into the view or query.  Otherwise Access is going to have to evaluate all rows everytime and blow up on the bad data.

0
 

Author Comment

by:joelmcm
Comment Utility
When I try the fix:
SELECT PID, HDID, IIF(ISNULL(OBSDATE)=True,'No Date',OBSDATE)
FROM 1ML_RPTOBS_CMPPG
WHERE HDID=28
The results are very curious. First I see my select table with values for all three (PID,HID, and OBSDATE). But when I try to see ALL the records by scrolling down, I get the same error message and all three columns turn into "name#".
0
 

Author Comment

by:joelmcm
Comment Utility
How can I create and Oracle view from Access? These downloads are done every night in the wee hours. I hae access ODBCing into Oracle, but no access to do anything with the Oracle table in Oracle.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
Comment Utility
What BPeb is saying is you create a view in Oracle. then you link that in. You need to ensure the right privileges are set so users have access to that view, probably a public synonym

Is HDID always holding a integer value?

U could try this

SELECT PID, CINT(HDID), IIF(ISNULL(OBSDATE)=True,'No Date',IIF(ISDATE(OBSDATE)=False,'Bad Date',OBSDATE))
FROM 1ML_RPTOBS_CMPPG
WHERE CINT(HDID)=28



0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Assisted Solution

by:BPeb
BPeb earned 250 total points
Comment Utility
Weird, I entered a response similar to what follows last night around nine, but it's not here now.

You have to go to Oracle to and create the view there.  Using a tool like Toad, Navigator, or PL/SQL Developer you can do it pretty easily.  The idea here is to have Oracle prevent Access from seeing any invalid data, so use the NVL command (like NZ in VBA) to fix any fields you're worried about.  

Second thought is to have the Oracle data swept and cleaned up or at least put a bogus date in the null date fields in case you ever need to query the whole table.

Finally, I've used Access to Oracle ODBC a lot over the past four years and it's a flaky connection.  The simplest change to a query can cause a crash, or slow it to a crawl.  The more you can have Oracle do in advance the better off you are.  It's more stable and faster that way too.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
BPeb, what ODBC driver did u use? The Microsoft one or the Oracle one? I am forced to use the Microsoft one because of the users using different versions of Oracle, but I prefer the Oracle one. Its handy cos it gives u more meaningful error messages when password expires.
0
 
LVL 5

Expert Comment

by:BPeb
Comment Utility
I tried both and felt the MS one better (more stable) at translating the ODBC calls.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
BPeb, Thanks!
0
 
LVL 5

Expert Comment

by:BPeb
Comment Utility
You're welcome.  Lets hope joelmcm  can get access to the Oracle side and try it, but you know how DBA's are!  :)
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Yes, at my place its all paperwork. Even to get one synonym created, I have to raise a change request then get it authorised by a high level manager. No wonder company loses money!
0
 

Author Comment

by:joelmcm
Comment Utility
Thank you all for your assistances. I did have a "view" created, but it was constructed as some kind of static file, and does not change over time.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now