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

ID: Q150401

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

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

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.

MORE INFORMATION

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.

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

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

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

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 about programmatically writing data to files, search the Help Index for "Open statement" or ask the Microsoft Access 97 Office Assistant.

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

Additional query words: align right justify open statement

Keywords          : kbprg PgmObj 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998