Is it possible to query blank cells?

  • Thread starter Thread starter Iwilfix
  • Start date Start date
I

Iwilfix

I have a field containing y and n entries.
but a large portion of this field have no data in them.
is there a criteria that will isolate these.
I tried a Not "y" and Not "n" line.
but then only get 1 blank cell in the outcome.
and there are about 1500 cells in this field without
data which I need to update without changing the
other entries.

thanks in advance Jeff V.
 
Often (usually?) string fields that have no value are Null, not blank.

Try WHERE [MyField] IS NULL

If you want to get fields that are null or blank, you can use

WHERE Len(Trim([MyField] & "")) = 0
 
Iwilfix said:
I have a field containing y and n entries.
but a large portion of this field have no data in them.
is there a criteria that will isolate these.
I tried a Not "y" and Not "n" line.
but then only get 1 blank cell in the outcome.
and there are about 1500 cells in this field without
data which I need to update without changing the
other entries.

thanks in advance Jeff V.
Click to expand...

Instead of 'not' try '<>'

You can also test directly for "" which is the empty string. If you
don't get records then, the value is probably Null, and can be queried
using 'Is Null'
 
Douglas said:
Often (usually?) string fields that have no value are Null, not blank.

Try WHERE [MyField] IS NULL

If you want to get fields that are null or blank, you can use

WHERE Len(Trim([MyField] & "")) = 0
Click to expand...

Wouldn't a (myfield & "")="" be sufficient?
 
Bas Cost Budde said:
Douglas said:
Often (usually?) string fields that have no value are Null, not blank.

Try WHERE [MyField] IS NULL

If you want to get fields that are null or blank, you can use

WHERE Len(Trim([MyField] & "")) = 0
Click to expand...

Wouldn't a (myfield & "")="" be sufficient?
Click to expand...

I agree that the Trim may not be required, but I've been dealing with SQL
Server tables a lot recently, and they can contain embedded blanks, so I
always include that. Suprisingly enough, using the Len() = 0 is more
efficient.
 
Douglas said:
I agree that the Trim may not be required, but I've been dealing with SQL
Server tables a lot recently, and they can contain embedded blanks, so I
always include that. Suprisingly enough, using the Len() = 0 is more
efficient.
Click to expand...
Oh sure, I wasn't aware of non-Jet tables in the reply.

Len() is probably faster because of the way strings are stored. a
Compare, albeit with an empty string, needs several handling steps
(IMHlow-level programming experience)
 
Dank je well, Bas

Jeff


Instead of 'not' try '<>'

You can also test directly for "" which is the empty string. If you
don't get records then, the value is probably Null, and can be queried
using 'Is Null'
Click to expand...
 
Back
Top