How do I add leading zeros in an amount field and remove the decimal point?

[stateemk]

I'm sure there's an obvious answer for this, but it's not coming to me. I have a make table query that figures a calculated amount for one column. I need to convert this table to a text file with no spaces or delimiters. The calculated field needs to be 8 characters with no dollar sign and no decimal point. How do I get the leading zeros in the file and the decimal point out?

[ADezii]

How about?

1. Posting the Table Name
2. Posting the Field Names and their Data Types
3. Post some sample Data
4. Example of how you would like the Exported Data to appear

[stateemk]

Currently there are three columns in this table. The first column is the EmpID which is a text field with 9 characters. The second column is SAM2down which is 10 characters. The third column is dedamt which is a calculated field. Its format is a number field with a double field size and Auto decimal places. Below is some sample data from the table.

EMP_ID SAM2down dedamt
999999999 MSECCMSECC 3
888888888 MSECCMSECC 5
777777777 MSECCMSECC 16
666666666 MSECCMSECC 40
555555555 MSECCMSECC 1
444444444 MSECCMSECC 7.5
333333333 MSECCMSECC 2

Here is what I would like it to look like in the text file.

999999999MSECCM SECC00000300
888888888 MSECCMSECC00000 500
777777777 MSECCMSECC00001 600
666666666 MSECCMSECC00004 000
555555555MSECCM SECC00000100
444444444MSECCM SECC00000750
333333333 MSECCMSECC00000 200

[hjozinovic]

stateemk,

I would first create such field in query and then take it out to a text file.
To create such field you can use expression in your query like this:

FinalString: [EMP_ID]&Format(Fix([dedamt]) & Right(Format([dedamt];"#,00");2);"00000000")

Please note here that i'm using symbols ; and , as it is adjusted to my local settings (Croatia)
You might need to replace some of those with your own smbols, but you should get the result.
regards,
h.

[ADezii]

Assuming your Table Name is Table2, the following code will Export the contents of the 3 Fields in this Table to a Text File named Results.txt in the Root Directory of Drive C:, namely (C:\Results.txt ). You can change the Path of the Constant (conOUTPUT_FILE ) to any Folder/File name you like. There will be no Delimiters between the Fields, and no Decimal Points in the Calculated Field. The results of the Calculated Column will be as you requested. You can just as easily run this code against the Query itself and not Create the Table, if you so desire.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Const conOUTPUT_FILE As String = "C:\Results.txt"

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table2", dbOpenForwardOnly)

Open conOUTPUT_FILE For Output As #1

With rst
  Do While Not .EOF
    Print #1, ![EMP_ID] & ![SAM2down] & Format((100 * ![dedamt]), "00000000")
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing
Close #1

[stateemk]

Thanks for the suggestions. I will try tomorrow to see what happens and let you know.