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
Expand|Select|Wrap|Line Numbers
- SET ref = left(ref,1) & "00" & right(ref,3)
Expand|Select|Wrap|Line Numbers
- SET ref = left(ref,1) & "0" & right(ref,4)
I have tried the following SQL and countless other variations but keep getting a syntax error.
Expand|Select|Wrap|Line Numbers
- 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]))
Thanks in advance
Gareth