NULL values

[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

 

[Rick Brandt]

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.

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 = ""

 

[Douglas J. Steele]

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 = ""

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

 

[Deborah Mowry]

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.

 

[Joel Wiseheart]

There is a possible workaround, although not really a
solution. I run into similar problems extracting data from
an old, proprietory HP OS with a database written in
FORTRAN.

What I have to do in that case is to use the linked table
in a make-table query, and create a new table. Then, I run
all of the queries from the new table, with whatever
filtering criteria that I want, and it seems to work fine.
I use the autoexec macro sometimes to refresh the table
when the database is opened. It's not exactly real-time
data, but is a possible workaround for less-than-friendly
ODBC links.

 

[Deborah Mowry]

I appreciate any ideas; however, making a new table from my query did not
change anything about the field content. I still cannot query/filter on Null
values.
I also changed the Allow zero length and Unicode compression to every
possible combination and that also did nothing.

This is most frustrating. It can be done on any table in Access but not this
imported one.
Is there any chance it is the middle step - meaning, they first exported it
as a Lotus WK4 file then I made a table by importing the WK4 file. The
Contact manager does not go directly to Access.

Just a thought.
Deborah

 

[Germain Vachon]

esque je peu recevoir du francais