• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1372
  • Last Modified:

Oracle NVL function

I have an oracle database that I am retrieving data with Access.  One of the columns I am retrieving can be null.  I need to interogate the column for certain values.  However, I need to convert the nulls to some value so they are included in the comparision.

In SQL, this is what I want to do:

select PROD_ID from table1 where nvl(PROD_ID,'00') not in ('08','09')

How can I do this in Access?
1 Solution
I'm not sure, but you might want to try isnull(Field,Value)
I guess NZ(PROD_ID,'00') is the function you're looking for.

Well, from Access point of view Nico is right and as the question was about NVL equivalent in Access he does deserve all those points :) , but I doubt it's was a good question - I think you are not handling Oracle backend in a proper way. Your query will retrieve all records from Oracle and will filter them locally - you loose all that client-server benefits. Better keep your NVL syntax and use pass-through query, then all the filtering job will be done by Oracle.

Good luck
select PROD_ID from table1
where PROD_ID is not null
and PROD_ID not in ('08','09')
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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