Setting multiple field sizes to same size at once

[MarkB]

Not sure if this is the correct newsgroup for this post, but here goes:


I have imported ASCII data into an Access table. All of the fields show a
255 size. None of the data being imported is larger than 50 in field size.

For exporting purposes I need all fields to be 40 or less in size for each
field. Is there a way to gloablly replace the field size in the Table
Design?

Also, when changing field sizes indivieually, it seems rather kludgy to
click the field, then move down to field properties to cilck in field size
and the tab to save value then go back up and click on another field and
repeat... is there some other design view where you can go down the column
called field size and just change values?

 

[Guest]

Hi Mark-

I can't answer the issue of simultaneously changing the size of multiple
fields, but perhaps on the other points:

1- FWIW, You can use the F6 function key to toggle back & forth between
panes in a table's design view & just use Enter or Tab to move to the next
row. Also, F4 opens the combo boxes so you can arrow/enter to select or you
can just type the first character of a data type & not even open the list,

2- When you Export, right-click the table, choose Export and set your file
type to Text, name the export file, etc. When you click the 'Export' button
you will be intercepted by a simple Wizard process. When you specify Fixed
Width & click 'Next' you can easily adjust the width of each field as you
will see, or you can click 'Advanced' and specify Start & Width values
numerically. You can then save the Specs (all-inclusive) & reuse them as
necessary so you don't have to reset them each time.

HTH |:>)

 

[Vincent Johns]

Not sure if this is the correct newsgroup for this post, but here goes:

I have imported ASCII data into an Access table. All of the fields show a
255 size. None of the data being imported is larger than 50 in field size.

For exporting purposes I need all fields to be 40 or less in size for each
field. Is there a way to gloablly replace the field size in the Table
Design?

Also, when changing field sizes indivieually, it seems rather kludgy to
click the field, then move down to field properties to cilck in field size
and the tab to save value then go back up and click on another field and
repeat... is there some other design view where you can go down the column
called field size and just change values?

I suggest that you set up a Query to display all of the fields in your
Table. Easy way to start is to open the Query in Query Design View,
show the Table in the upper pane, and run down the Table's fields,
double-clicking each one.

Having done that, use Build to replace the first field reference, say
[MyTable].[MyField1], with an expression that truncates it to 40
characters, such as Left$([MyField1],40). Save the Query and run it to
see that it works, then reopen it in Query Design View and switch to SQL
view. You'll see something like this:

SELECT Left$([MyField1],40) AS Expr1,
MyTable.MyField2,
MyTable.MyField3
FROM MyTable;

At this point, and I'm assuming you have many (dozens? hundreds?) of
fields, you can use Notepad or a word processor to change the remaining
fields to look like the first one, via a global search-and-replace
operation.

You will need to be sure you've done this carefully enough that you
still have valid SQL code when you're done, such as making sure that the
new field names are all different. Having done that, replace the SQL in
the Query with your new version.

(Actually, my copy of Access 2000 is reasonably forgiving, and it
accepted the following not-too-standard version:

SELECT Left$(MyField1,40),
Left$(MyField2,40),
Left$(MyField3,40)
FROM MyTable;

Access automatically added the "AS Expr1", etc., parts when I saved the
Query using this SQL.)


Run the revised Query to see if it works the way you'd like, then if so,
export its results.

Warning: The method I suggested here will NOT notify you if any data are
lost due to truncation of long values.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Ed Robichaud]

Under "Tools - options" you can set the default text field size in your
tables for each database.
-Ed