Update a table based on field length

[Gareth Jones]

Hello all,

I have a table with reference numbers along with a number of other fields.

The ref numbers start with B and vary in length, 4 - 6 characters, but need to be 6 characters long.

If the ref is 4 characters in length, I need to add 00 after the B. This works fine

 SET ref = left(ref,1) & "00" & right(ref,3)

If the ref is 5 characters in length, I need to add 0 after the B. This works fine

SET ref = left(ref,1) & "0" & right(ref,4)

The problem I have is updating the whole table depending on whether the ref is 4 or 5 characters long.

I have tried the following SQL and countless other variations but keep getting a syntax error.

Update Test
iif(len([ref])<5,SET ref = left(ref,1) & "00" & right(ref,3),iif(len([ref])<6,SET ref = left(ref,1) & "0" & right(ref,4),[ref]))

Anyone have any ideas?

Thanks in advance

Gareth

[Rabbit]

The set ref = needs to be outside the iif function.

Another way of doing it would be to prepend a bunch of 0s and take the right 6.

[NeoPa]

The following should work fully flexibly :

SET [Ref]=Format(Val(Mid([Ref],2,5)),'B00000')

It will handle the numeric text as a number and format it the way that you stipulate.

[Gareth Jones]

Thanks both for replying. I tried the one from NeoPa and it worked perfectly :) I had tried the format method but couldn't get the SQL right.

Thanks again.

[NeoPa]

There are other approaches to this, of course, but that seemed the most elegant and straightforward once the requirement is fully understood. Thankfully that was clear from your OP, which is why we could help so quickly.

Your mission, should you choose to accept it, is to look at it and see why it makes sense ;-)