Sally,
The proper way to do this is to use VBA code. Here's a sample:
Function export_pipe_delimited()
Dim flen(5) As Integer
Dim rst As DAO.Recordset
flen(0) = 4
flen(1) = 20
....
Open "PathAndFileName" For Output As #1
Set rst = CurrentDb.OpenRecordset("TableName")
rst.MoveFirst
Do Until rst.EOF
lin = "|"
For i = 0 To rst.Fields.Count - 1
lin = lin & rst.Fields(i)
For j = 1 To flen(i) - Len(rst.Fields(i))
lin = lin & " "
Next
lin = lin & "|"
Next
Print #1, lin
rst.MoveNext
Loop
rst.close
Set rst = Nothing
Close #1
End Function
Note:
Paste the code in a standard module and make the following cahnges, then
save. Also while in the VB editor window, choose menu Tools > References and
click Microsoft DAO 3.x Object Library (3.51 for Access 97, 3.6 for Access
2K or later).
Changes:
In line "Dim flen(5) As Integer" change 5 to the number of fields in the
table minus 1 (i.e. 5 is for six fields)
Use as many field length value assignments as your fields (like flen(0) = 4,
flen(1) = 20.... stands for first field length 4, second field length 20
etc.
Change PathAndFileName to the actual path and file name
Now run the code, and your job is done.
If you're not comfortable with VBA, there is a "friendlier" alternative...
you can use a simpe select query, and substitute the field names in the grid
with expressions like:
Exp1:=[Fiedl1] & left(" ",10-Len([Field1])) & "|"
where Field1 is the name of the field, 10 is the resired length for the
field in the text file and " " is just a string of spaces which is
at least as long as the desired field length. Just repeat for all fields
applying the correct field name and desired length.
Then, export the query to a text file.
Still, this alternative is not a good one...I strongly urge you to try the
VBA one... after all, you'll get there eventually anyway, so the sooner the
better; once crossed, noone's ever come back!
HTH,
Nikos
