what will be the select query for excluding empty values in ms access

Question

I am trying

select * from table where Contact Is Not null

but it is displaying values including empty values

Could you please update your question that you are looking for query which will exclude the empty strings also along with null values. — Panther, Jun 16 '15 at 07:12
why you have down voted. Now i have changed null to empty @panther — Vampire, Jun 16 '15 at 07:14
I have downvoted previously, now when you have corrected . I am upvoting again — Panther, Jun 16 '15 at 07:16

Barry · Accepted Answer · 2015-06-16T07:07:41.600

1

Your query is correct but probably you have zero length strings in your Contact column. You can use

select * from table where len(Nz(Contact, '')) > 0

The Nz function returns the specified default value if the column is null.

edited Jun 16 '15 at 07:07

answered Jun 16 '15 at 07:03

Barry

@Vampire I would recommend taking a read through http://allenbrowne.com/vba-NothingEmpty.html it was a great read to learn more about Null's, Nothing's, and Empty strings. – Newd Jun 16 '15 at 12:34

Rahul Tripathi · Answer 2 · 2015-06-16T07:53:12.277

0

You can try like this:

select * from table where Len([Contact] & "")>0

edited Jun 16 '15 at 07:53

answered Jun 16 '15 at 06:57

Rahul Tripathi

This query displaying null values.. I want to display not null values – Vampire Jun 16 '15 at 07:01
@Vampire:- Updated my answer. Please check now! – Rahul Tripathi Jun 16 '15 at 07:02
displaying same result (null values). – Vampire Jun 16 '15 at 07:04
@Vampire:- Then there is a very much possibility that your column has empty strings with which you are getting confused and hence getting the empty values which you think is NULL – Rahul Tripathi Jun 16 '15 at 07:06
The length must be greater than 0. You can than loose the `Is Not Null` check because `null & ""` will always return `""`. – Barry Jun 16 '15 at 07:09

2 Answers2