Re: replace null values

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
Date: Thu, 28 Sep 2006 18:38:55 -0600

On Thu, 28 Sep 2006 15:56:02 -0700, maggie
<maggie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Is there a way to replace all null values by zeroes in a table?
I need to find all null values in all fields for several tables, the UPDATE
query only allows me to replace one field at a time, and I have hundreds of
fields to search and replace.

Thanks,

I agree with Dale that it's probably not necessary to do this... but
if you do wish to do so, you only need one query per table. Update
each field to

NZ([fieldname])

This will update the field to 0 if it is NULL, and to its current
value (i.e. leaving it unchanged) if it isn't.

John W. Vinson[MVP]
.

Prev by Date: Re: permissions problem?
Next by Date: Re: Plotting price changes.
Previous by thread: Re: replace null values
Next by thread: Re: One to many back to one string
Index(es):
- Date
- Thread

Relevant Pages

Re: Corstabquery or normal query
... You can create the crosstab similar to the suggestion by John. ... church, also church elders, we have many elders, only 1st elder is one person. ... I would first build a query to get the data ...
(microsoft.public.access.reports)
Re: Creatinig a database to update in alphabetical order
... John M. it is like you are reading my mind, ... know I am a movie fanatic. ... > should not be routine) displayed as a query datasheet. ... > Keyword ...
(microsoft.public.access.gettingstarted)
Re: Simply adding two fields
... Thank you, John. ... In an update query you update the field to the calculation. ... field before you add it to the master field you will have a problem. ... I'm reading a transaction file of widgets delivered and I want to update ...
(microsoft.public.access.queries)
Re: problem with query
... Thanks John, it is not a number field, it is text. ... Open tblSitLog and look at the field SIT - what type of field is ... You may need to write the query as follows ... , tblSITLog.[NATURE OF iNCIDENT] ...
(microsoft.public.access.queries)
Re: set first record to zero in a calculated field
... John, after playing around with the FOrmat comand, I think I managed to get ... I resolved it in my previous query by using the Format command in the SQL, ... Center for Health Program Development and Management ...
(microsoft.public.access.queries)