ACC: Exporting Right-Aligned Fields to a Text File (95/97)

• Microsoft Access versions 7.0, 97

SUMMARY

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

This article demonstrates two methods of creating 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.

MORE INFORMATION

Method 1

The following steps demonstrate how to use a query to create 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.

1. Open the sample database Northwind.mdb, and create a new query based on the Orders Table.

2. 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 10.
   
   

3. Run the query. Note that the fields may not appear to be right- justified 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.

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

5. In the Save In box, under Save As Type box, Select "Text Files." Type "Orders.txt" (without the quotation marks) in the File Name box, and then click Export. This will invoke the Text Export Wizard.

6. Select "Fixed Width" and click Advanced. This displays the Export Specification dialog box.

7. In the Field information table of the Export Specification dialog box, type the Field Name, Start, and Width for each field as follows:

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

8. Click Save As to save the specification as Right Justify Orders.

9. 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-justified while the OrderDate and Freight fields are right-justified.

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.

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Create a new module.

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

4. Press CTRL+G to open the Debug window, type the following line, and then press ENTER to run the function:

         ? CreateTextFile()
   

5. Open the Orders.txt file in NotePad. Note that the CustomerID field is left-justified while the OrderDate and Freight fields are right- justified.

REFERENCES

For an example of this information for Microsoft Access versions 1.x and 2.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q148444
   TITLE:      ACC: Exporting Right-Aligned Fields to a Text File(1.x,
               2.0)

For more information and an alternative solution to right-justify number fields only, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q98663
   TITLE     : ACC: Exporting to Fixed-Width Text File Left-Aligns
               Numbers