How to export fixed length records to a data file.

[wdsnews]

I'm creating a query for our school using the school district's standard
student data format. All fields are fixed width. For example the
WhiteEthnicFg field is exactly one character of either "Y" or "N". In our
database we record that field as a Boolean. The Date-of-Birth District's
field is an 8 character field in the format mmddyyyy. Most fields are fixed
width 'Char' fields, but I don't know how to make the output match the
correct width. There are decimal fields 'with one implied decimal point'.
Example: 0000100 will result in 000010.0. The final character of each
record must be a [cr] or [lf].

How should I go about this? Is a query and export the right way to create
the file?

 

[Allen Browne]

There are 2 steps to achieving your goal:
1. Set up a query that gives you the data formatted the way you need.
2. Use TransferText to export to a fixed width text file.

1. Query
In the Field row of query design, type expressions that format the data for
you. Examples:
IIf([WhiteEnthnicFg], "Y", "N")
Format([Date-Of-Birth], "mmddyyyy")
Format(Val(Nz([Amount],"0")), "0000000.0")

2. TransferText
Use the query as the source of a TransferText action (in a macro) or method
(in VBA code.) You can specify fixed widith.

If necessary, you can set up an import/export specification to define the
number of characters to use for each field. You do this by manually going
through the export wizard (File menu.) Once you have the wizard running,
there is an Advanced button that lets you create the import/export spec.