Exporting Access Table to Text

[Joyce]

My Access field is formatted as Number showing leading zeros for a field up to 15 spaces - my numeric input is variable (any # from 1 to 10,000) so I need my data to be right justified with leading zeros.

I got that part just fine.

But, when I export it to a .TXT file, it drops my zeros Does anyone know how I can keep all my zeros as I output to Fixed Width Text?

Thanks for your time & advice.

 

[mdmilner]

I'm not sure how to fix your problem directly, but, if you have a relatively small amount of data, you might consider using recordsets you might try:

You may need to add code to control what is passed to strVal, depending on the fieldtypes being sent to the file. There's additional code and approaches to how you might concatenate multiple fields from a single record into the strVal variable -- either explicitly naming the fields or just reading all of them (however many are there). Not sure how much 'help' you need.

Mike

Public Sub TextExport()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strPath, strVal As String

Set dbs = CurrentDb()

strSQL = "SELECT * FROM tblName"
Set rs = dbs.OpenRecordset(strSql, dbOpenSnapShot)

strPath = "path_to_file_to_save"
Open strPath For Append As #1

With rs
  Do Until rs.EOF
    strVal = .Fields(0).Value
    Print #1, strVal
    .MoveNext
  Loop
End With

Close #1

Set rs = Nothing
Set dbs = Nothing
End Sub

 

[Joyce]

Thank you for your time & help; unfortunately Im not at all familiar with code so I couldn't get it to work. Instead I used a complicated IF statement. Lucky for me I didn't have to go over 15!

JE AMT: IIf(Len([JE AMOUNT])=1,'00000000000000',IIf(Len([JE AMOUNT])=2,'0000000000000',IIf(Len([JE AMOUNT])=3,'000000000000',IIf(Len([JE AMOUNT])=4,'00000000000',IIf(Len([JE AMOUNT])=5,'0000000000',IIf(Len([JE AMOUNT])=6,'000000000',IIf(Len([JE AMOUNT])=7,'00000000',IIf(Len([JE AMOUNT])=8,'0000000',IIf(Len([JE AMOUNT])=9,'000000',IIf(Len([JE AMOUNT])=10,'00000',IIf(Len([JE AMOUNT])=11,'0000',IIf(Len([JE AMOUNT])=12,'000',IIf(Len([JE AMOUNT])=13,'00',IIf(Len([JE AMOUNT])=14,'0',[JE AMOUNT])))))))))))))) & [JE AMOUNT]

 

[SydneyGeek]

Hi Joyce, this is simpler:

=RIGHT('000000000000000' & [JE AMOUNT],15)

You can adapt this for any fixed-length output by changing the number of zeros and the second parameter in the expression.

Denis

 

[Joyce]

Thank You! This is perfect - and I can easily use it for other applications.