forcing field size in make table query

[Gary]

I am running a make table query to build a table I need to
export to text as fixed width. The query returns the
correct values but the each text field that I define in
the query comes out as a 255 character field in the
table. Is there a way to set the length of a text field
in a make-table query?

 

[Douglas J. Steele]

None that I'm aware of, but it doesn't really matter. Access only takes as
much space as is required. This is easily proven: there's a limit of 2000
characters per row, yet you can create a table with, say, 20 fields each of
text 255. You won't get an error message until the first time you try to
insert a row with 2001 or more characters/

 

[Van T. Dinh]

Personally, I would set the (empty) Table Structure first and then use
Append Query to insert Records to this Table rather than using the
Make-Table Query.

 

[Jim Russell]

Don't worry about doing it in the make table query. When you export the
table (or a query) to a text file you get to set up the column positions and
length for each field (in Access 2K at least.)

If you are going to do it more than once, you can save and reuse your
definitions. It is not as intuitive as it might be, but use the "Advanced"
button on the text export wizard to set up and save/load your definition.
(It is similar to the test import wizard.
Couple of other hints...
Remember that you don't export an open table or query, instead you select
the query name in the database window (F11) then select Export from the file
menu. You must specify a destination type and location, then the wizard will
appear and let you define the output format.

The wizard will format your date columns based on your selections, trying to
format a date in your query will not work.

You can't skip columns on the output, be sure your query selects only the
columns you need to export to text.

BTW if you need better control over a make table specification, create an
empty table with the specifications you need (field sizes, key field,
autonumber, etc.) and they use an Append query.
(For future loads, delete existing rows first.)