Microsoft KB Archive/150401

= ACC: How to Export Right-Aligned Fields to a Text File (95/97) =

Article ID: 150401

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q150401



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



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

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.



Method 1
The following example shows you how to use a query to create a fixed-width text file. This example uses the Orders table in sample database Northwind.mdb. In this example, the CustomerID field will be left-aligned while the OrderDate and Freight fields will be right-aligned.  Open the sample database Northwind.mdb, and create a new query based on the Orders Table.  Create the following three fields in the query: Field: CustomerID

Field: Expr1: Space(12-Len(Format([OrderDate],"Short Date"))) & Format([OrderDate],"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 will be used again in Step 7.  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. You can ignore this because it does not affect the text file. Save the query as Right Justify Orders. On the File menu, click Save As/Export. In the Save As box, click to select "To an external File or Database," and then click OK. In the Save In box, under Save As Type box, Select "Text Files." Type Orders.txt in the File Name box, and then click Export. This will invoke the Text Export Wizard. Select "Fixed Width" and click Advanced. This displays the Export Specification dialog box.</li>  In the Field information table of the Export Specification dialog box, type the Field Name, Start, and Width for each field as follows: <pre class="fixed_text">     Field Name   Start   Width --  -   -      CustomerID     1       10 Expr1        11       12 Expr2        23       15 </li> Click Save As to save the specification as Right Justify Orders.</li> Click OK to return to the Text Export Wizard, and click Finish to create the text file. Open the Orders.txt file in NotePad. Note that the CustomerID field is left-aligned while the OrderDate and Freight fields are right-aligned.</li></ol>

Method 2
The following sample function, CreateTextFile, creates a fixed-width text file using the Orders table in Northwind.mdb. The CustomerID field will be left-justified while the OrderDate and Freight fields will be right-justified. <ol> Start Microsoft Access and open the sample database Northwind.mdb.</li> Create a new module.</li>  Type the following function: Public Function CreateTextFile

'This function creates a fixed-width text file using the Orders table 'in Northwind.mdb. The CustomerID field will be left-justified 'while the OrderDate and Freight fields will be right-justified.

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 Dim mydb As Database, myset As Recordset Dim intFile As Integer

Set mydb = CurrentDb Set myset = mydb.OpenRecordset("Orders", dbOpenTable)

myset.Index = "PrimaryKey" 'Orders table must have primary key. intFile = FreeFile

Open "C:\My Documents\Orders.txt" For Output As intFile

'The following section is optional. Remove the comment (') 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. myset.MoveFirst Do Until myset.EOF LSet strCustomerId = myset![CustomerID] 'Field name in brackets RSet strOrderDate = Format(myset![OrderDate], "Short Date") RSet strFreight = Format(myset![Freight], "Currency") 'Concatenate all of the variables together as in the following: Print #intFile, strCustomerId & strOrderDate & strFreight myset.MoveNext Loop

Close intFile myset.Close mydb.Close

MsgBox "Text file has been created!"

End Function </li> Press CTRL+G to open the Debug window, type the following line, and then press ENTER to run the function:

? CreateTextFile</li> Open the Orders.txt file in NotePad. Note that the CustomerID field is left-justified while the OrderDate and Freight fields are right- justified.</li></ol>

<div class="references_section">