Null values in fields

[Jeff]

I'm using an Access 2000 database with nearly a million
records, but a good number of fields have "null" values in
them. I wanted to update all of the null values with some
kind of data to keep it sortable and searchable.

I tried multiple ways of using an update query with no
success and from Access Help, I tried to use the Find and
Replace feature, which says it can find Null values by
typing "null" or "is null" and making sure the 'Search is
formatted' box is checked - but this doesn't work either.
I also tried to find zero-length fields by using "" - but
this didn't work either. Maybe I'm missing something in
my queries. If anyone has an update / append query that
locates and replaces Null values, can you post it up?

Thanks!

 

[Allen Browne]

You can assign a value to the fields with a query such as these:
UPDATE MyTable SET MyNumberField = 0 WHERE MyNumberField Is Null;
UPDATE MyTable SET MyTextField = "" WHERE MyTextField Is Null;

However, I do not believe that this will be productive. Nulls are
searchable, and sortable, and meaningful - an important aspect of database
theory and practice.

 

[Jeff]

Thanks for the help Allen. I had actually played around
some more and used this:

UPDATE Table SET Field = 'N/A' WHERE IsNull(field) = True;

so it was basically the same thing. I agree about the
Nulls, but in this particular case where I'm cross-
referencing part numbers, null values are nearly useless
so I had to put some data in there.

 

[John Vinson]

Thanks for the help Allen. I had actually played around
some more and used this:

UPDATE Table SET Field = 'N/A' WHERE IsNull(field) = True;

so it was basically the same thing. I agree about the
Nulls, but in this particular case where I'm cross-
referencing part numbers, null values are nearly useless
so I had to put some data in there.

Just a note - this will work for a Text field, but the text string
"N/A" will not be allowed in a number or date type field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps