Changing field size of text via macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day,

I have a macro which runs several queries and finally makes a table of that
compiled information. This final table is where I need to change the field
size of a text field from 5 to 6, however I don't seem to be able to do that
via a macro. I'm wondering if there is an expression I can use within the
make table query? If tried a calculated field within the query (looking at
one of Steve C's examples) such as Exportfield: [FieldName]/1. However, this
makes this field a double, when I'm trying to maintain the text data type.
Is there something similar for text data type fields? Thank you for your
time and assistance.
 
AZ,

Are you using a Make-Table Query? I think if you do like this...
Exportfield: [FieldName] & ""
.... it will create a Field Size of 255. This will probably be ok? Any
reason for wanting it to be 6? Does it matter? Any reason you can't
just change the field size of the field in the table you are basing the
query on?

Another approach would be to use a DDL query, using the CREATE TABLE
syntax to make a table to your specifications, and then use an Append
Query to put your data in there.
 
Yep it was a make table query. Here's a little background. Before, the user
would have to run one macro which ran all of the queries and the make table
query (which we've been discussing). The make table query would always put a
certain field size to five. The user would then need to open this table,
change the field size to six and then run another macro which finished the
entire process. The purpose of a field value of six instead of five was due
to append queries further along in the process which appended data with six
characters.

What I did to fix this is change the make table query to an append query. I
preceded this append query with a delete query. Therfore the original table
(and properties) are retained, therefore elimintating the need to change the
field size values. I may use your expression though, it still seems the
simpler solution. Thank you for your help.

Steve Schapel said:
AZ,

Are you using a Make-Table Query? I think if you do like this...
Exportfield: [FieldName] & ""
.... it will create a Field Size of 255. This will probably be ok? Any
reason for wanting it to be 6? Does it matter? Any reason you can't
just change the field size of the field in the table you are basing the
query on?

Another approach would be to use a DDL query, using the CREATE TABLE
syntax to make a table to your specifications, and then use an Append
Query to put your data in there.

--
Steve Schapel, Microsoft Access MVP

AZ said:
Good day,

I have a macro which runs several queries and finally makes a table of that
compiled information. This final table is where I need to change the field
size of a text field from 5 to 6, however I don't seem to be able to do that
via a macro. I'm wondering if there is an expression I can use within the
make table query? If tried a calculated field within the query (looking at
one of Steve C's examples) such as Exportfield: [FieldName]/1. However, this
makes this field a double, when I'm trying to maintain the text data type.
Is there something similar for text data type fields? Thank you for your
time and assistance.
Click to expand...
Click to expand...
 
Back
Top