What's a Solution or Formula to export a text field with padded spaces in Access 2003, to a txt file

Question

Hello

Im using Access2003 database, with 2 tables, and a single query, a macro to view the files, and a macro to export the files (delimited, not fixed), and a form.

The Main table is built as follows: "Employee Name" ;data type: text ,field size:22. "Employee ID" ;data type: text ,field size:22. "RT #" ;data type: text , field size:9. "A/C Number" ;data type: text ,field size:15. "Amount" ;data type: number ,field size:Decimal , Format:Standard ,Precision:9 ,Scale:2 ,Decimal Places:2. "Effective Day" ;data type:text ,field size:2. "Status" ;data type:text ,field size:255.

The secondary table "Active Table" is built as follows: "Employee Name" ;data type: text ,field size:22. "Employee ID" ;data type: text ,field size:22. "RT #" ;data type: text , field size:9. "A/C Number" ;data type: text ,field size:15. "Amount$" ;data type: text ,field size:255. "Effective Day" ;data type:text ,field size:2. "Status" ;data type:text ,field size:255.

The query is built as follows: "Employee Name" "Employee ID" "RT #" "A/C Number" "Amount$: Format$([Amount],"000000.00")" "Effective Day" with criteria "[Please enter Effective Day]" "Status" with criteria "Active"

There are 2 Macros 1. View: Open Query (Active ACH by day), Open Form (frm_Veiw_Active_Table) 2. Export: TransferText (transfer type:Export Delimited; table name:Active Table)

There is one Form frm_Veiw_Active_Table

There is also a switchboard with options: Add & Edit; View Selected Day Export Selected Day Exit

How the Swithcboard Functions: 1. Users add rows or edits rows 2. The users will click on "View Selected Day", and enter the Day e.g.3 (In the background, the View Macro runs and query filters for Active persons with an Effective day of 3; and read only results are displayed) 3. The users will click on "Export Selected Day" (In the background, the Query replaces the value in "Active Table", with the results for the chosen Effective Day in step 2. The Export Macro then exports this result into a .txt file on the desktop)

The export file has varying lengths for all the text fields from the Active Table, with the exception of the Amount$ field which exports with a fixed amount of 9 characters (whatever amount is added in the Main table, is padded with 0's up to 9 characters by the Query, so it is exported up to 9 characters with the leading 0's).

I need to set a fixed lenght for all the other exported text fields e.g. Employee name should be 22 characters always, and there by should be padded with blank spaces to amount to 22 characters if the persons name is shorter than 22 characters.

Does anyone know how to add a formula to my Query, to pad my text fields with invisible characters e.g. up to 22 for Employee Name, so that when the file is exported, it has a fixed amount of characters (letter and invisible)

Any help will be greatly appreciated.

Just to note: i did try setting up my Export Macro as Transfer Type: Fixed Length; but this cuts off my last two colums Effective Day and Status (these are my 2 fields with criteria added in the query)

Answer 1

To pad or truncate a string to 22 characters:

myNewString = left(myString & space(22),22)

You add 22 spaces to the end then you take the leftmost 22 characters of the string you have made.