Need help formatting data for export, filling in zeros

[Guest]

I need to add zeros to the leading part of a number that I'm exporting. The
numbers are being exported as text and have three or four digits. i.e. "845"
or "1435" I need to have them export as "00000845" or "00001435". Seems
like it should be easy but I'm going in circles. Any suggestions would be
greatly appreciated.

P.S. I am a geek (Network Engineer) but not a programmer so be gentle.

 

[MH]

Use the FORMAT function in your query:

Format(MyField, "00000000")

MH

 

[louisjohnphillips]

I need to add zeros to the leading part of a number that I'm exporting. The
numbers are being exported as text and have three or four digits. i.e. "845"
or "1435" I need to have them export as "00000845" or "00001435". Seems
like it should be easy but I'm going in circles. Any suggestions would be
greatly appreciated.

P.S. I am a geek (Network Engineer) but not a programmer so be gentle.

This function's input parameters are an integer and its desired zero-
filled format length. It prepends a zero-filled string to the string
value of the number. It returns the rightmost nLength characters.
Try using this logic:

Private Function ZeroFillInteger(nWholeNumber As Integer, nLength As
Integer) As String

Dim str0000 As String

Dim sZeroFilled As String

str0000 = "00000000000000000000000000000000000"

sZeroFilled = Right$(str0000 + CStr(nWholeNumber), nLength)

ZeroFillInteger = sZeroFilled

End Function

Lou Phillips

 

[Joseph Meehan]

I need to add zeros to the leading part of a number that I'm
exporting. The numbers are being exported as text and have three or
four digits. i.e. "845" or "1435" I need to have them export as
"00000845" or "00001435". Seems like it should be easy but I'm going
in circles. Any suggestions would be greatly appreciated.

P.S. I am a geek (Network Engineer) but not a programmer so be gentle.

What are you exporting them to? Numbers by nature do not have leading
zeros. They can be displayed that way in Access and other programs, but the
don't really have the zeros.

I have never had the need so I don't have any experience doing this.
There may well be a way of creating an output to a text file with leading
zeros, but you may need to change the field type to text.

I wonder if it might be easier to add the zeros back in the destination
program.

 

[Grover Park George]

The Format Function affects the way values are DISPLAYED, but not the way
they are stored. Leading zeros, in other words, only exist in text strings.

You can use the CStr function to coerce the data type for export.
CStr(Right("00000" & [YourNumberField]))

 

[Guest]

Thanks a veriation on this worked well.

Jeff

The Format Function affects the way values are DISPLAYED, but not the way
they are stored. Leading zeros, in other words, only exist in text strings.

You can use the CStr function to coerce the data type for export.
CStr(Right("00000" & [YourNumberField]))

--
George Hepworth
2007 Access MVP

I need to add zeros to the leading part of a number that I'm exporting.
The
numbers are being exported as text and have three or four digits. i.e.
"845"
or "1435" I need to have them export as "00000845" or "00001435". Seems
like it should be easy but I'm going in circles. Any suggestions would be
greatly appreciated.

P.S. I am a geek (Network Engineer) but not a programmer so be gentle.

 

[Guest]

I am exporting to a text file (.csv) that needs to be in a specific format to
imported into a payroll system.

I have an answer. But thanks for your interest.

Jeff

 

[MH]

The format function returns a string:

"Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression."

It has nothing to do with the way values are DISPLAYED or STORED.

MH

The Format Function affects the way values are DISPLAYED, but not the way
they are stored. Leading zeros, in other words, only exist in text
strings.

You can use the CStr function to coerce the data type for export.
CStr(Right("00000" & [YourNumberField]))

--
George Hepworth
2007 Access MVP

I need to add zeros to the leading part of a number that I'm exporting.
The
numbers are being exported as text and have three or four digits. i.e.
"845"
or "1435" I need to have them export as "00000845" or "00001435". Seems
like it should be easy but I'm going in circles. Any suggestions would be
greatly appreciated.

P.S. I am a geek (Network Engineer) but not a programmer so be gentle.