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

access sql isnull

I have a customers table with an ID, and two fields for phone number: phone_primary and phone_alternate.

I want to create a query. that will pull up ID and phone which could be primary_phone.    However, If primary_phone field has a NULL value, then it should display the phone_alternate instead.     But I don't want a 3 column retrieval, and both phone_primary and phone_alternate.

I'm using Access.   What's the SQL syntax for such?

I thought I should start with ISNULL() and was able to retrieve 0 and -1 (for False), but not sure where to go from there?

Any thoughts?

2 Solutions
Bill RossCommented:

Try this.

Select ID, iif(isnull([phone_primary]),[AlternatePhone],[phone_primary]) AS PhoneNumber FROM  Customers;

The key here is the iif  it has 3 parts separated by a comma.  Paert 1 is the test, part 2 is the true part 3 is the false - so if the phone_primary is null then show the secondary in the column PhoneNumber...


Patrick MatthewsCommented:
If you need to substitute another value for a null, you can also use the Nz function:

Select ID, Nz([phone_primary], [AlternatePhone]) AS PhoneNumber FROM  Customers;
hansabenAuthor Commented:
Thanks very much!
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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