Is Null Criteria (Access 2007) (1 Viewer)

[dreamz]

Hi

Have a problem with the Is null statement/criteria in access 2007.

If i put the Is null criteria in, it returns 1 row (there are alot more blanks)

If i return all the records and filter for blanks, it returns 200 rows.

What could the problem be?

 

[Banana]

It usually helps to post the full SQL.

One thing you could test is if it's a text field, check for how many

=""

you get. There are no visible difference between Null and zero length string though they're definitely different.

 

[dreamz]

It usually helps to post the full SQL.

SELECT B12
FROM TK3000
WHERE (((B12) Is Null));

One thing you could test is if it's a text field, check for how many

=""

you get. There are no visible difference between Null and zero length string though they're definitely different.

returns the blanks and non blanks

 

[Banana]

Are you saying that this query:

SELECT B12 
FROM TK3000
WHERE B12 = "";

returns both blank and nonblanks?

I was expecting blanks only but the point of exercise is verify that if you have both Null and Zero Length String, you'll see "blank" cells for the either query. There are no visible difference between a Null or a Zero Lengh string; they both look 'blank' to us.

Thus if you have to account for both, your query should be:

SELECT B12
FROM TK3000
WHERE B12 IS NOT NULL AND NOT (B12 = "")

 

[datAdrenaline]

How was this table created? What is the datatype (assuming text, however, if you have a Lookup Table defined at the table/field level, you may visually experience blanks, when in fact the lookup just does not have a match) ...

... given all this, and the assumption that the field is a Variable TEXT datatype ... Give this a shot:

To return the blanks:

SELECT B12
FROM TK3000
WHERE (B12 Is Null) Or (B12 = "");

To return those that are NOT blank:

SELECT B12
FROM TK3000
WHERE B12 > ""