Export numbers into text file

[ELG]

Is there a way to export a table or query containing text and numbers including decimals to a text file and have the numbers right aligned? I can pad the fields with 0's, but I don't want the 0's in the end result. The file will eventually be input into a mainframe table.

Thanks,
Ed

 

[Bachenot]

Hi ELG

One way to have the numbers all the same length in a text file is, as you say, to pad the field with zeros.
First, find the length of the highest number; say it has five digits.
Make a new field as a text field.
Make your new field have a length of 5.
Then use the select case approach to concatenate zeros in front of the number.
So for numbers 1-10, you would have 4 zeros
numbers 11-99, you would have 3 zeros
numbers 101-999, you would have 2 zeros
numbers 1000-9999, you would have 1 zero.

Once on the mainframe, designate the field as numeric and the leading zeros should disappear.

 

[TonyF]

I use this function to pad numbers all the time. It allows you to pad any length and by changing the pad character, any format.

Hope this helps

Kind regards

Tony

'----------------------------------------------------------
' Function:     LPad0 (myvalue, MyPaddedLength%)
'
' Description:  Pads out a string with 0 in front of the
' value to the length specified
'               
'-----------------------------------------------------------
Function LPad0 (MyValue As Variant, MyPaddedLength%)
'-----------------------------------------------------------
Dim PadLength As Integer, X As Integer, PadString As String

    If IsNull(MyValue) Then MyValue = "0"
    
    PadLength = MyPaddedLength - Len(MyValue)
    
    For X = 1 To PadLength
        PadString = PadString & "0"
    Next
    
    LPad0 = PadString + MyValue

End Function

 

[PaulF]

or consider using Space(). Capture the length of the number, and subtract that from a maximum length and use that value as the number of spaces and store both to a string variable.

i.e.

strNumber = Space(10 - len(Number)) & Cstr(Number)

PaulF