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

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.
joelmcmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
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
joelmcmAuthor Commented:
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
rockiroadsCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BPebCommented:
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
joelmcmAuthor Commented:
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
joelmcmAuthor Commented:
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
rockiroadsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BPebCommented:
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
rockiroadsCommented:
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
BPebCommented:
I tried both and felt the MS one better (more stable) at translating the ODBC calls.
0
rockiroadsCommented:
BPeb, Thanks!
0
BPebCommented:
You're welcome.  Lets hope joelmcm  can get access to the Oracle side and try it, but you know how DBA's are!  :)
0
rockiroadsCommented:
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
joelmcmAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.