output to .dat file with field lengths

[randalsp]

HELP.

I have 2 small tables.
Table2 with 4 columns and Table1 with 10 columns

I have Query1 that links the 2 tables.

Query 1 shows exactly what i wish to see.

I want to export to a .dat file using VBA

I have tried multiple things but not quite got it working as i would like

I get something like this:
----------------------------------------------------------------------
| VENDORID | BARCODEID | DELIVERYNOTE |
----------------------------------------------------------------------
| AP | XXX000 | 1111111111 |
| AP | XXX111 | 2222222222 |


1. I do not want the colum headings
2. i dont want the | & -
3. i need the field spacings after each bit of data.

for example. if VENDORID was 12 characters, i would like AP followed by 10 blank spaces. At the moment i get 1 tab space

This file gets imported elsewhere so it has to be 100% accurate with spacings.

i currently have something basic like this "DoCmd.OutputTo acQuery, "Query1", acFormatTXT, "filename.txt""

it doesnt seem to let me do .DAT with this but the file must be a .dat file also

Can anyone help ?
Many Thanks
Steve

 

[DJkarl]

The easiest way to do this is to create an export specification and use the TransferText method to output the file. Using the built-in method I don't believe you can export directly to a .dat file but in VBA you could use the Name function to rename the file from a txt to a dat.

 

[John.Woody]

You could consider using the "print #" command to create the file and populate it by running through a recordset. You could also create a function to get the length of your data and add the relevant number of spaces, or shorten the string if necessary, and use this as you run through the recordset.