return the greatest length for each field in a table

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

Guest

I'm working on cleaning up my tables and giving them the correct field sizes.
A lot are defaults and are just way too big. I would like bring these field
sizes down, but I am concerned about losing data.
Does anyone know of an easy way to create a query that would return the
greatest length value for each field? In other words ...
Name Hobby FavoriteFlower Turtle_Name
20 85 135 52
Any help is greatly appreciated.
Thanks,
Cleech
 
If it's for a one off type exercise you could use
the 'Len' function on each of the fields and then just
manually sort the table on the various columns to see the
highest number in the respective fields (set the 'field'
on a query to Len([Name]) )
 
SELECT Max(LEN(FieldNameA)) as MaxFldA,
Max(Len(FieldNameB)) as MaxFldB,
Max(Len(FieldNameC)) as MaxFldC
FROM YourTable

That said, Access only uses what it needs in any field. It isn't absolutely
necessary to limit field lengths in order to save space. It does help though to
keep users from entering too many characters into one field (or series of
fields) and then exceeding the maximum 2000 character limit for a record. That
limit does not include the characters in a memo field.
 
Back
Top