Set Field Length in Make Table Query (1 Viewer)

[Evan Robitaille]

I have a query that creates a new text field using the following:

REL_ACCT: Left([CRAPPL],1) & "18" & Format([CRACCT],"0000000000")

This creates a 13-character field for me which is what I want. An example of the new field would be "D181122334455" The number of characters in the [CRACCT] field needs to be ten and I use the format to pad those with only nine characters. When I use this in a Make Table query, the field length of this new text field changes to 255 in the new table that's created.

How can I set the length to 13 in the original query, please??

Many Thanks,

 

[stopher]

You can't set the text length in the query afaik. The point being at theat point you are not actually storing any data (in the query).

You can change the table definition after you have run your make table query:

ALTER TABLE myTablename
ALTER COLUMN myFieldName text(13);

You could run your first query and this one sequentially in a macro.

The other way to do this is to create your destination table first with all its constraints. Then append the data to the table.

hth
Chris

 

[boblarson]

Whenever I hear of someone making a "Make Table" query, it sets off alarms (potential normalization issues, etc). While there may be occasional times when this is necessary, more times than not it is not necessary to create a table with a make table query. My question, just to get clarification as to a good way to respond, would be why are you doing this?

 

[BrianDP]

I know it's been several years since anyone posted - but I think I have a good reason for a create table query.

I have a situation where I have parts lists saved to a SQL server. It's a three tiered situation, Jobno - Headerno - DetailParts. And, when things go normally, the person checks out their job, it creates a copy of their Jobno on their computer - it copies the associated Headers, and detail parts onto their local computer. They print, and can make changes to it, and then it copies the tables back into the master on server.

I need a create table query when they create a NEW job, because it doesn't have the table from the server to define what the detail table should look like. I have it use a create table to copy the job master records into the working job. This thread was helpful because it was creating a 255 character field when I really wanted a 30 character field.

After it adds the field, I issue:

db.Execute "ALTER TABLE [detailparts] alter Column barcode text(30)"