NULL values

  • Thread starter Thread starter Deborah Mowry
  • Start date Start date
D

Deborah Mowry

Working with a downloaded table form a Lotus Notes contact manager. Now in
access call tblclients.
I query certain info. to update the records.
BUT...'Is Null' does not work. It comes back empty and there are definitely
empty contents in the fields I chose. I know how to do this as it works in
my other tables on other databases.
The only difference I can see in this table as opposed to tables created
directly in Access is the Field Design property called Unicode Compression
saying No instead of the default Yes.

I changed the Unicode to Yes in case this had something to do with it and
that resulted in no change.

Any ideas? There are many records with empty contents and it is up to me to
update the contents. There are 60,000 records, I have to be able to query
for Null values.

Deborah
 
Deborah Mowry said:
Working with a downloaded table form a Lotus Notes contact manager. Now in
access call tblclients.
I query certain info. to update the records.
BUT...'Is Null' does not work. It comes back empty and there are definitely
empty contents in the fields I chose. I know how to do this as it works in
my other tables on other databases.
The only difference I can see in this table as opposed to tables created
directly in Access is the Field Design property called Unicode Compression
saying No instead of the default Yes.

I changed the Unicode to Yes in case this had something to do with it and
that resulted in no change.

Any ideas? There are many records with empty contents and it is up to me to
update the contents. There are 60,000 records, I have to be able to query
for Null values.
Click to expand...

I may be that the fields contain zero length strings rather than Nulls. Test for
both and see if it works.

WHERE SomeField Is Null OR SomeField = ""
 
Rick Brandt said:
I may be that the fields contain zero length strings rather than Nulls. Test for
both and see if it works.

WHERE SomeField Is Null OR SomeField = ""
Click to expand...

I've always preferred:

WHERE Len([SomeField] & "") = 0

or, even better

WHERE Len(Trim$([SomeField] & "")) = 0

(Believe it or not, it's faster to check for a length of zero than to
compare it to "". And it's faster to use vbNullString than "", but
unfortunately I don't believe you can use the constant in a query.)
 
I did a query for "" and it most certainly worked. I don't understand why
though! I have never done it before, what is different in this table that
made it 'zero length strings' as you mentioned? I changed the Unicode thing
and there is nothing typed in those fields. How is the import being saved
and is there something I can change to put it the way I would have expected
it to work...meaning other users will expect to type Is Null.

Please advise and thank you very much for the answer.
 
Back
Top