Formatting issue

Hi All,
I have a query which I have to export in a DAT format.All the fields
have been formatted except for one field.This field I need to add
leading and trailing zeros.
example; if a number is like 1234 I have to get it to be
000000001234000.If a number is like 123456 then it has to be
000000123456000 or if a number is 123456789 then it should be
000000123456789.It all adds to a 15 digit format.

As you can see it is not consistent or in a way where I can use the a
format function or the Lpad or Rpad.Or maybe I am missing something.
Please suggest a solution which has me stumped.

Happy Thanksgiving!
Thanks,

Roy

Re: Formatting issue

On Nov 20, 3:15 pm, Roy wrote:
> Hi All,
> I have a query which I have to export in a DAT format.All the fields
> have been formatted except for one field.This field I need to add
> leading and trailing zeros.
> example; if a number is like 1234 I have to get it to be
> 000000001234000.If a number is like 123456 then it has to be
> 000000123456000 or if a number is 123456789 then it should be
> 000000123456789.It all adds to a 15 digit format.
>
> As you can see it is not consistent or in a way where I can use the a
> format function or the Lpad or Rpad.Or maybe I am missing something.
> Please suggest a solution which has me stumped.
>
> Happy Thanksgiving!
> Thanks,
>
> Roy

Roy:

Without knowing the 'rules' of when to pad to the left and/or right
with zero's, we won't be able to help you out with specifics. Is
there any rhyme or reason to the adding the zero's?

If your 'rule' is based on the length of the original string, it would
be something like this:

Function MyPaddingFunction(stMyString as String) as String
Select Case Len(stMyString)
Case 1
stMyPaddedString = String(10,"0") & stMyString & String(4,"0")
Case 2
stMyPaddedString = String(9,"0") & stMyString & String(4,"0")
Case Else
do something else here
End Select
End Function

Then, you would use your function by calling it like this:
=MyPaddingFunction(YourFieldWithOriginalNumber)

HTH,
Jana