IsNULL and LEN differences

R

Roy

Hi all,
In a table there is a number field that for the half of the rows doesn't
have values. When I'm running a query as IsNULL([FieldName])=True, I get
those half the records that FieldName doesn't have values. But when I run
Len([FieldName])=0, I don't get any record not even one. Can someone tells me
what I'm missing here.
Thanks in advance,
Roy
 
G

George Nicholson

To add to Lynn's response, if you want to test for both empty strings and
Nulls at the same time, convert Nulls to empty strings & then use Len():

Len(Nz([FieldName],""))=0
 
D

Duane Hookom

Or
Len([FieldName] & "") = 0

--
Duane Hookom
MS Access MVP


George Nicholson said:
To add to Lynn's response, if you want to test for both empty strings and
Nulls at the same time, convert Nulls to empty strings & then use Len():

Len(Nz([FieldName],""))=0

--
George Nicholson

Remove 'Junk' from return address.


Roy said:
Hi all,
In a table there is a number field that for the half of the rows doesn't
have values. When I'm running a query as IsNULL([FieldName])=True, I get
those half the records that FieldName doesn't have values. But when I run
Len([FieldName])=0, I don't get any record not even one. Can someone
tells me
what I'm missing here.
Thanks in advance,
Roy
Click to expand...
Click to expand...
 
G

George Nicholson

Len([FieldName] & "") = 0

awhhh, that's so cute :)

--
George Nicholson

Remove 'Junk' from return address.


Duane Hookom said:
Or

--
Duane Hookom
MS Access MVP


George Nicholson said:
To add to Lynn's response, if you want to test for both empty strings and
Nulls at the same time, convert Nulls to empty strings & then use Len():

Len(Nz([FieldName],""))=0

--
George Nicholson

Remove 'Junk' from return address.


Roy said:
Hi all,
In a table there is a number field that for the half of the rows doesn't
have values. When I'm running a query as IsNULL([FieldName])=True, I get
those half the records that FieldName doesn't have values. But when I
run
Len([FieldName])=0, I don't get any record not even one. Can someone
tells me
what I'm missing here.
Thanks in advance,
Roy
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

A public Happy Anniversary to Lynn and his tolerant wife!
Click to expand...

Hear hear!

Thirty years... and she hasn't even applied to the parole board for an
early release due to good behavior!

John W. Vinson[MVP]
 
Top