How to change a column format with SQL

[TTR9000]

This is the sql code I have now and it works fine but I need it to change the column Proto_matl_list.uic_upc to only have 11 characters in it. it currently has 12. I have tried to do this but I don't have much experience with SQL


SELECT Proto_matl_list.itemno, Proto_matl_list.uic_upc[/color] INTO [proto matl]
FROM Proto_matl_list;

 

[jmersing]

All you have to do is place an alias on the end of the field and rename it whatever you want

SELECT Proto_matl_list.itemno, (Proto_matl_list.uic_upc [/color]) as ProtoUPC INTO [proto matl]
FROM Proto_matl_list;

If this is an append query it looks like you are missing the word Insert, I only see Into, that will not work.

 

[TTR9000]

This is a make table query. and i dont know if i understand what you are trying to tell me what to do. Could you go into more detail. Right now that field is by default formatted as text with 50 char and i want it formatted as text with 11 char.

 

[anvil19]

Hi TTR9000

According to Getz, Litwin & Gilberts' Access 2000 Developers Handbook Volume 1 - the Access bible if ever there is one- it states that

Tables created by SELECT INTO statements will not contain primery keys, indexes, or any column or table properties other than the defaults assigned to any new table.

Page 180 of the paperback eition published 1999.

To alter the properties of a table you will need to use Jet 4.0 extentions for the ALTER TABLE statement with ALTER COLUMN, like so:

ALTER TABLE <TablenameHere> ALTER COLUMN <ColumnNameHere> SET DEFAULT "<Default-Value-Here>";

Back up your database, and try that.

anvil19

 

[TTR9000]

But where do I put that jet extension? See I have never used sql or jet so I need a little extra help.

 

[anvil19]

Hi TTR9000, me again

You would add this SQL string into an Access Query. Go to queries, click new and then choose design. Cancel the box that pops up with your tables and query names. Click SQL on the left hand side of the query design toolbar. The SQL box will appear. Then just type in your SQL statement. When you run the query, the system will apply the changes to the table in the statement. Once the query has run you can exit the query without saving.

You do not need to know how the Jet 4.0 extentions work, just that they are available to you via Access. Your statement will be something like so:

ALTER TABLE <TablenameHere> ALTER COLUMN <ColumnNameHere> TEXT(11);

There are no warnings that the table is to be altered so make sure you really want to do what you are doing.

anvil19