export numeric field right alligned


J

janssensglb

I would like to export from Access 2000 a numeric field (double) to a
text
file. The export file should be fixed length (no delimiters), the
field should have 9 digits in total, including 2 decimals. I want the
field to be right aligned so that the decimal separator is always in
the same column in the text file. I don't want to fill any leading
blanks with zeros.
I tried to do this using the format function in a query but I always
end up with a left aligned records which causes the decimal separator
to shift according to the length of the field.
Can anyone help me?
Thanks
 
Ad

Advertisements

J

janssensglb

Thanks but with this format, leading blanks are filled with zeros
which is not what I was looking for...
 
Ad

Advertisements

P

Pieter Wijnen

Use spaces in stead

Public Function LPad(ByVal PadValue As Variant, Optional ByVal PadLen As
Integer = 10, Optional ByVal PadCh As String = " ") As String
On Error Resume Next

If VBA.Len(PadValue) >= PadLen Then
LPad = PadValue
Else
LPad = VBA.String(PadLen - VBA.Len(PadValue), PadCh) & PadValue
End If

End Function

HTH

Pieter

Usage (Your sample)
LPad(Format(myField,'0.00'),9)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top