I am formatting a table to be exported via fixed width and one of the main frame requirments is to have the dollar amount with leading zeros. So if a dollar amount is 500.02, I would need to update the tables dollar amount to 00000000000050002. Since the dollar amount will change, I will only need the leading zeros for the blank spaces because the field is right justified (for the mainframe).

Hopefully I am making sense.

Thanks for your help.

Some thing like below should work. The first parameter is you dollar amount and the second is how long in length it needs to be. Let me know if this works.

Public Function FormatNumber(Num As Currency, Length As Integer)
Dim cNum As Currency
Dim lNum As Long
Dim sNum As String
Dim iCurlength As Integer
Dim iTotalLength As Integer

iTotalLength = Length

cNum = Num

sNum = cNum

sNum = Replace(sNum, ".", "")

iCurlength = Len(sNum)

For icurlenght = Len(sNum) To iTotalLength
sNum = "0" & sNum
Next

FormatNumber = sNum


End Function

i know the answer is probably yes, but does it have to be fixed width, rather than csv

its just that fixed width will be hard to manage, but csv is intrinsic to access, and wil be plain sailing

I haven't tested, but it might be simpler to multiply your value times 100 and then use the Format() function to pad the zeros:

Format(YourField * 100, "00000000000000000")

That presumes there's no values with more than 2 decimal places. If so

Format(Int(YourField * 100), "00000000000000000")

Thank you all for helping. I got it to work.

Have a great day!

Why not use the fixed width options in the export specification?

i didnt realise you could do that, neil - never looked hard, i suppose as i use csv/excel exports almost exclusively

Thank you all for helping. I got it to work.

Have a great day!

Others searching the forum for help might like to know which option you used.

Brian