CODEWITHSUNDEEP
ms-accessvb6
Phani
Phani

Reputation: 23

Ms access query for dont show blank filed

I have a query that has a couple of text fields where in the filter criteria I put <> "Power". When I put this criteria in, it also does not show me records where the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields.

How do I get the query to exclude the criteria Power but still show records where that field is blank?

Upvotes: 1

Views: 4314

Answers (4)

Pedro
Pedro

Reputation: 4160

Try adding an OR IS NULL: 

<> "Power" OR IS NULL

Upvotes: 2

HansUp
HansUp

Reputation: 97101

A blank field could mean the field is Null or it contains a zero length string (""). You can test for either of those conditions by concatenating the field with another zero length string. If the string length of that combination is zero, you know the field must be either Null or zero length string.

SELECT *
FROM YourTable
WHERE
       Your_column <> 'Power'
    OR Len(Your_column & '') = 0;

Upvotes: 1

wqw
wqw

Reputation: 11991

Try using ANSI SQL function COALESCE like this

SELECT *
FROM MyTable
WHERE COALESCE(MyColumn, '') <> 'Power'

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

SELECT *
  FROM YourTable
 WHERE 'T' = SWITCH(
                    your_col IS NULL,     'T', 
                    your_col = ' ',       'T', 
                    your_col <> 'Power',  'T'
                   );

Upvotes: 1

Related Questions