Trying to pad leading zeros in export

[tagteam]

I am exporting to a flat file from a query and need to pad zero's

I have tried .VarCarNumber = Nz(rs!Format([CarNumber], "000000"), "") but it says external name not defined.

I am using VarCarNumber As String * 6 when i declare the variables.



Currently with .VarCarNumber = Nz(rs![CarNumber], "") it exports like TCIX6302 TE170403492190R315 - as you can see it is left aligned and has no leading zero.

Any ideas how I can make this work best. When i try to do it in the query it wont allow CarNumber: Format([CarnUmber],"000000"), it says Circular reference caused by alias "CarNumber in query definitions select list.

Thanks

[JoeM]

Are you trying to do this in a query or VBA?
If VBA, please post your VBA code.
If in a query, you wouldn't use a "rs!" reference in front of the Format function.

Also, what is the Data Type of CarNumber? The Format function only works on numeric fields, not text fields.
What is the total length of the desired result?

[tagteam]

I use a query and export it using VBA

I was able to get it to work by formatting the output after TextFile.WriteLine I used Format(.VarCarNumber, "000000")

[rkeifer]

Here is a UDF which also works


Function PadLeft(parmString As String, ParmChar As String, ParmLen As Integer) As String
'--------------------------------------------------------------------------
' parmString - string you want padded
' parmChar - character used for padding left
' parmLen - final length of returned string
'--------------------------------------------------------------------------
If Not IsNull(parmString) Then
PadLeft = String$(ParmLen - Len(Trim$(parmString)), ParmChar) & Trim$(parmString)
Else
PadLeft = ""
End If

End Function