IsNULL and LEN differences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
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...
 
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

avoid #error 2
Parsing Text Field In Access and using NZ 1
Access query translate to SQL Server 1
Create IN criteria from another table 1
IsNull Parameter with Cross Tab 1
Problem with Median Calculation / VBA Script 2
Median calculation 2
Using ISNULL() 17

Back
Top