Populate empty fields with a number

[Paul Wilson]

I have a a column which has 3 possibilites, 1,2 or 3. I have already
populated fields with either a 1 or 2 based on another query. I wish to
populate the remainder of fields which have no content with 3.

I have tried to write the query as UPDATE dbase SET dbase.[field] = 3 WHERE
dbase.[field] <> 1 AND <> 2. But this doesn't work, and have tried UPDATE
dbase SET dbase.[field] = 3 WHERE dbase.[field] = "", and this doesn't work
either. Data type mismatch occurs on last query.

Any help would be great?

Cheers

 

[Dale Fye]

Paul,

You might try:

UPDATE dbase
SET dbase.[field] = 3
WHERE ISNULL(dbase.[field])

or

UPDATE dbase
SET dbase.[field] = 3
WHERE dbase.[field] <> 1
AND dbase.[field] <> 2.

HTH
Dale

 

[John W. Vinson]

I have tried to write the query as UPDATE dbase SET dbase.[field] = 3 WHERE
dbase.[field] <> 1 AND <> 2. But this doesn't work, and have tried UPDATE
dbase SET dbase.[field] = 3 WHERE dbase.[field] = "", and this doesn't work
either. Data type mismatch occurs on last query.

The AND operator looks like the English language conjunction... but it isn't.
Your first try is taking the two expressions

dbase.[field] <> 1

and the expression

<> 2

and trying to determine the truth or falsity of each expression. Well, the
second one isn't a valid expression! You need to compare the field again:

[field] <> 1 AND [field] <> 2

The second try doesn't work either because a NULL value is not a string, and
it's not equal to the empty-string constant "".

Your best bet is

UPDATE dbase SET [field] = 3 WHERE field IS NULL;

Alternatively, if you want to overwrite any value (null, zero, 31512,
whatever) with 3 except for those records with 1 or 2:

UPDATE dbase SET [field] = 3 WHERE [field] NOT IN(1,2);

John W. Vinson [MVP]