I have a fieldname Employer_Code with different values such as 1133, 33001, 2211, 33223 ,...
Now I want to add 00 incase the code is four digit like 1133 and 0 if its five digit like 33001.
Num(Employer_Code,'00000') AS Employer_Code
if (left(Employer_Code,4) = '1133',
'00' & Employer_Code,
if (left(Employer_Code,5) = '33001',
'0' & Employer_Code,
) as New_Employer_Code
i don't know what your specific needs are but you can do something like this
=if(len(Employer_Code)=4,'Employer_Code'&00, if (len(Employer_Code)=5,'Employer_Code'&0) ))as something
if have not tried this but it might help you in the right direction
I think the best solution is the suggested by Felim with a little correction:
Num(Employer_Code,'000000') AS Employer_Code
This will automatically format the number to fill the leading zeros.
ahh yea I was appending a zero.... stupid me
Retrieving data ...