Help with IIf and LEN function

[Kirsti]

Hi,

I am having trouble using these functions together in a query. I have a set of data that could be 3 char or could be 4 char. If it is only 3 char, I need to put a zero in front.

I am trying:

IIf(Len([MyField]=3,"0"&[MyField],[MyField]))



But am getting an error saying that my expression has the wrong number of arguments.

Any help much appreciated.

Cheers,
Kirsti

[pbaldy]

You don't close the Len function correctly, but I'd do this:

Format(MyField, "0000")

[recyan]

Check

IIf(Len([myField])=3,"0" & [myField],[myField])

Thanks

Edit : Oops, was typing while pbaldy posted.

[Kirsti]

Thank you both - have it working perfectly now.

Kirsti

[pbaldy]

We were happy to help!

[LUMPKINBD]

I know I'm a little behind the times here, but I am trying to do something similar. Only I need to add a ".0" to end of any value that is 3 characters long.

My table is named "Master"
My column is named "DIAGNOSIS01"

My SQL Skills are Iffy at best.

Bust basically If LEN[DIAGNOSIS01]=3 then add ".0" to the end making 111 into 111.0

[Kirsti]

Try,

IIf(Len([DIAGNOSIS01])=3,[DIAGNOSIS01]&".0",[DIAGNOSIS01])

Good luck!

[LUMPKINBD]

Is that using an update query?

[Kirsti]

No, just an ordinary select query.



It would look like this...

[LUMPKINBD]

Just a little background, when i was given this project it came with 2 problems, both of which had to do with excel stripping data out before it got to the database. Now I get to fix it. the first issue was diagnosis codes come in this format xxx.x or xxx.xx with x being alphanumeric characters. The first problem was it stripped the 0's from the front of the of any value. i.e. 008.45 became 8.45 and 034.25 became 34.25. I used the following sql in an update query to fix that:

UPDATE Master
SET DIAGNOSIS01 = LEFT("00", 3-LEN(DIAGNOSIS01)) + DIAGNOSIS01
WHERE LEN(DIAGNOSIS01)<3 AND Len(DIAGNOSIS01)>0;

Now, the second problem is it stripped out the .0 from a bunch of values making 111.0 into 111

I figured that a variation of the above sql using "right" vice "left" and moving the other stuff around would work but I am at a stalemate.

[LUMPKINBD]

OK, I see where the confusion is, I need it to actually permanently update the values... That worked for returning a value with a ".0" on the end, but I need it to update the records on the master table.

[Kirsti]

Ok, but the code I posted above could be used in an update query (doesn't have to be a SELECT query). I tested it with dummy data in my DB and it worked. You would need to run it after your update query.

[LUMPKINBD]

So what would the update query look like, because I can't seem to get it to work. Sorry, I must be missing something...

[LUMPKINBD]

got it... thanks a ton.

UPDATE Master SET Master.DIAGNOSIS01 = IIf(Len([Master].[DIAGNOSIS01])=3,[Master].[DIAGNOSIS01] & ".0",[Master].[DIAGNOSIS01]);

[Kirsti]

Fantastic - glad to hear you got it working!