conditional update query returning empty field

[Guest]

'Tractbna' is a text field of size 6 in table 'sf312090'. Some values in
Tractbna are 4 characters long, others are 6. I want to add "00" to the end
of all the 4-character values and leave the 6-character values as they are. I
have added a new field 'newtract' to the table that is also a 6-character
text field.

This is the SQL I've written to fill 'newtract':

UPDATE sf312090 SET sf312090.newtract = IIf(Len(Trim([sf312090]![TRACTBNA]))
= 4, [sf312090]![TRACTBNA] & "00", [sf312090]![TRACTBNA]);

It returns an empty field. Where am I going wrong?

Actually, what I really want to do is:

UPDATE sf312090 SET sf312090.TRACTBNA = IIf(Len(Trim([sf312090]![TRACTBNA]))
= 4, [sf312090]![TRACTBNA] & "00", [sf312090]![TRACTBNA]);

but I want to make sure I have the syntax down first.

TYIA,
Marian

 

[John W. Vinson]

but I want to make sure I have the syntax down first.

Don't use IIF at all! Instead use a criterion:

UPDATE sf312090
SET sf312090.TRACTBNA = [sf312090].[TRACTBNA] & "00"
WHERE Len(Trim(TRACTBNA)) = 4;

The Trim() isn't necessary most likely - Access doesn't store trailing blanks.
If you have leading blanks you'll need it, and it won't hurt if there are no
blanks (other than slowing down the query).

John W. Vinson [MVP]