Microsoft KB Archive/210052

= ACC2000: How to Export Right- or Left-Aligned Fields to a Text File =

Article ID: 210052

Article Last Modified on 4/22/2003

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210052



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

This article applies only to a Microsoft Access database (.mdb).



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).



MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To create a fixed-width text file, use one of the following methods.

Method 1: Use a Query
The following example demonstrates a query that creates a fixed-width text file. This example includes the Orders table in the sample database Northwind.mdb. As a result of this sample query, the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.  Open the sample database Northwind.mdb, and then 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 pads spaces at the beginning of the field. The numbers 12 and 15 at the start of the Space function indicate the intended 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 right-aligned unless you are using a fixed-width font such as Courier. You can ignore this appearance because it has no effect on the text file. Save the query as Right Justify Orders. On the File menu, click Export. In the Save as type box, click Text Files (*txt;*.csv;*.tab;*.asc). Type Orders.txt in the File name box, and then click Save. This starts the Text Export Wizard. Click the Fixed Width - Fields....each field option, and then click Advanced. This displays the Export Specification dialog box.</li>  In the Field Information table, type the following Start and Width data: <pre class="fixed_text">  Field Name   Start   Width --  -   -   CustomerID     1       10 Expr1        11       12 Expr2        23       15 </li> Click Save As. In the Specification Name box, type Right Justify Orders, and then click OK.</li> Click OK to return to the Text Export Wizard, and then click Finish to create the text file. When the message appears, click OK.</li> Open the Right Justify Orders.txt file in Microsoft NotePad. Note that the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.</li></ol>

Method 2: Use a VBA Function
The following sample function, CreateTextFile, creates a fixed-width text file from the Orders table in Northwind.mdb. The CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned. <ol> Start Microsoft Access, and then open the sample database Northwind.mdb.</li> In the Database dialog box under Objects, click Modules, and then click New.</li>  Type the following function in the module sheet: Public Function CreateTextFile

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

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 DAO.Database, myset As DAO.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 Immediate 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-aligned, and the OrderDate and Freight fields are right-aligned.</li></ol>

<div class="references_section">