Microsoft KB Archive/148444

= ACC: Exporting Right-Aligned Fields to a Text File (1.x/2.0) =

Article ID: 148444

Article Last Modified on 5/7/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q148444



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article demonstrates two methods you can use to create a fixed-width text file that enables fields to be either left- or right-aligned.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic" in version 2.0.



Method 1
The following steps demonstrate how to use a query and a macro to create a fixed-width text file using the Orders table in the sample database NWIND.MDB. The Customer ID field will be left-aligned and the Order Date and Freight fields will be right-aligned in the text file:

  Open the sample database NWIND.MDB, and create the following new query based on the Orders Table.

NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these expressions.

     Query: Right Justify Orders --

Field: Customer ID     Field: Expr1: Space(12-Len(Format([Order Date],"Short Date"))) & _ Format([Order Date],"Short Date")

Field: Expr2: Space(15-Len(Format([Freight],"Currency"))) & _ Format([Freight],"Currency")

NOTE: The Space function is used to pad spaces at the beginning of     the field. The numbers 12 and 15 at the start of the Space function indicate the desired total width of that field in the text file. These numbers are used again in step 5 below.  Save the query as Right Justify Orders, and then run the query.

Note that the fields may not appear to be right-aligned unless you are using a fixed-width font such as Courier. This can be ignored because it does not affect the text file. Close the query to return to the Database window. On the File menu, click Imp/Exp Setup.  In the Field Information table of the Import/Export Specification dialog box, type the Field Name, Data Type, Start, and Width for each field as follows:

     Field Name   Data Type  Start   Width --  -  -   -      Customer ID   Text        1       10 Expr1        Text        11      12 Expr2        Text        23      15 </li> Click the Save As button and save the specification as Orders Spec. Click OK to close the Import/Export Specification dialog box.</li>  Create a new macro as follows:

<pre class="fixed_text">     Macro Action TransferText

TransferText Actions Transfer Type: Export Fixed Width Specification Name: Orders Spec Table Name: Right Justify Orders File Name: C:\Orders.txt Has Field Names: No

NOTE: You can specify the name of a query in the Table Name argument. </li> Run the macro.</li> Open the C:\Orders.txt file using NotePad. Note that the Customer ID field is left-aligned and the Order Date and Freight fields are right-aligned. The field names will not be included in the first row of the text file. If you set the "Has Field Names" argument in the macro to Yes, the field names will be delimited instead of fixed-width. Use Method 2 if you want to include field names in the first row of a fixed-width text file.</li></ol>

Method 2
The following steps demonstrate how to use Access Basic to create a fixed-width text file using the Orders table in the sample database NWIND.MDB. The Customer ID field will be left-aligned and the Order Date and Freight fields will be right-aligned.

<ol> Open the sample database NWIND.MDB.</li> Create a new module.</li>  In the Declarations section of the module, add the following line:

<pre class="fixed_text">     Option Explicit </li>  Create the following function:

Function CreateTextFile 'This function creates a fixed-width text file using the 'Orders table in NWIND.MDB. The Customer ID field will 'be left-aligned and the Order Date and Freight fields 'will be right-aligned.

'Create a Dim statement for each field to export to the text 'file. For the data type of each field, use "String *" followed 'by the width of the field. Dim strCustomerId As String * 10 'Specifies width of 10 characters. Dim strOrderDate As String * 12 'Specifies width of 12 characters. Dim strFreight As String * 15   'Specifies width of 15 characters.

'Create a recordset based on the Orders table. Dim mydb As Database Dim mytable As Table Set mydb = CurrentDB Set mytable = mydb.OpenTable("Orders")

'Determine the index to sort the table by. mytable.Index = "PrimaryKey" 'A primary key must exist in the 'Orders table.

'Create the text file. Note the use of the RSet statement 'to right-align a field. You can use the LSet statement if you 'want to left-align a field (or simply assign the variable       'without using RSet or LSet.)

Dim intFile As Integer intFile = FreeFile Open "C:\Orders.txt" For Output As intFile

'The following section is optional. It puts the field names in the 'first row of the text file. Remove the comment mark (') from these 'lines if you want to put field names in the first row of the text 'file. 'LSet strCustomerId = "CustomerID" 'RSet strOrderDate = "OrderDate" 'RSet strFreight = "Freight" 'Print #intFile, strCustomerId & strOrderDate & strFreight

'This section puts the records from the Orders table in the text 'file. mytable.MoveFirst Do Until mytable.EOF LSet strCustomerId = mytable![Customer ID] RSet strOrderDate = Format(mytable![Order Date], "Short Date") RSet strFreight = Format(mytable![Freight], "Currency") 'Concatenate all of the variables together as in the following: Print #intFile, strCustomerId & strOrderDate & strFreight 'The following optional line can be used to create a blank row 'after each record. Remove the comment mark (') from the 'following line if you want to create a blank row after each 'line. 'Print #intFile, mytable.MoveNext Loop

Close intFile mytable.Close mydb.Close MsgBox "Text file has been created!"

End Function </li> Click Immediate Window on the View menu, type the following line, and then press ENTER to run the function:

? CreateTextFile </li> Open the C:\Orders.txt file using NotePad. Note that the Customer ID field is left-aligned and the Order Date and Freight fields are right-justified.</li></ol>

<div class="references_section">