Knowledge Base

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.

In the Field Information table, type the following Start and Width data:

   Field Name   Start   Width
   ----------   -----   -----
   CustomerID     1       10
   Expr1         11       12
   Expr2         23       15

Click Save As. In the Specification Name box, type Right Justify Orders, and then click OK.
Click OK to return to the Text Export Wizard, and then click Finish to create the text file. When the message appears, click OK.
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.

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.

Start Microsoft Access, and then open the sample database Northwind.mdb.
In the Database dialog box under Objects, click Modules, and then click New.

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

Press CTRL+G to open the Immediate window, type the following line, and then press ENTER to run the function:
```
?CreateTextFile()
                    
```
Open the Orders.txt file in NotePad. Note that the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.

REFERENCES

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

For more information about programmatically writing data to files, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type open statement in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about an alternative solution to right-aligning number fields only, click the article number below to view the article in the Microsoft Knowledge Base:

201115 ACC2000: Exporting to Fixed-Width Text File Left-Aligns Numbers

Additional query words: align justify

Keywords: kbhowto kbinfo kbprogramming KB210052

Microsoft KB Archive/210052

Contents