IIf function

[MTSPEER]

Hello,

I am trying to create an IIf function where it gets the length of the field. if it is under the length of 5 I want to add a "0" to the beginning of the field. I cannot get the query to work. It just deletes everything in the field.

UPDATE Import_Template SET Import_Template.Zip_Code = IIf(Len([Zip_Code]>5),"0"+[Zip_Code],[Zip_Code]);




I don't know what to put in as the false statement.

[davegri]

 IIf(Len([Zip_Code])<5,"0"+[Zip_Code],[Zip_Code]);

Change the parens this way. And change the GT to LT ( < )
For zip_code to display the leading zero, it has to be a text field.
Still a problem if the zip_code is less than 4 characters.
You could run the query 4 times to take care of those lesser lengths.

[MTSPEER]

It is a text field

[MTSPEER]

Ahh nevermind got it to work thanks. It was the parenthesis..

[ArviLaanemets]

A general solution will be:

a) If ZIP-code max length is 5 characters
Right("00000" & ZipCode,5)
b) If Zip-code max length can be longer than 5 characters, but not less than 5 charcters
Iif(Len(ZipCode)>5, ZipCode, Right("00000" & ZipCode,5))