How to determine maximum size of field contents

  • Follow


How do I query the maximum size of the contents of a given text field?  Also, 
any suggestions on how to write a query to return the maximum size of the 
contents of each text field in my database?

I'm using Access 2003.

Thanks,
Pat
0
Reply Utf 11/6/2007 4:06:02 PM

Len(fieldName) will give you the size of the contents of the field.

For any one table you can do the following query.
SELECT Max(Len(FieldA)) as ASize
, Max(Len(FieldB)) as BSize
, Max(Len(FieldC)) as CSize
, Max(Len(FieldD)) as DSize
FROM YourTable

If you want to get the max len of each field of each table, then I would 
write a VBA procedure to step through every table and every field and store 
the results in a table for reporting/analysis.


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Pat" <Pat@discussions.microsoft.com> wrote in message 
news:1825CDB2-27CE-466A-A110-DB0D39296225@microsoft.com...
> How do I query the maximum size of the contents of a given text field? 
> Also,
> any suggestions on how to write a query to return the maximum size of the
> contents of each text field in my database?
>
> I'm using Access 2003.
>
> Thanks,
> Pat 


0
Reply John 11/6/2007 4:35:00 PM

Thanks John, that's what I needed to know!

Pat

"John Spencer" wrote:

> Len(fieldName) will give you the size of the contents of the field.
> 
> For any one table you can do the following query.
> SELECT Max(Len(FieldA)) as ASize
> , Max(Len(FieldB)) as BSize
> , Max(Len(FieldC)) as CSize
> , Max(Len(FieldD)) as DSize
> FROM YourTable
> 
> If you want to get the max len of each field of each table, then I would 
> write a VBA procedure to step through every table and every field and store 
> the results in a table for reporting/analysis.
> 
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Pat" <Pat@discussions.microsoft.com> wrote in message 
> news:1825CDB2-27CE-466A-A110-DB0D39296225@microsoft.com...
> > How do I query the maximum size of the contents of a given text field? 
> > Also,
> > any suggestions on how to write a query to return the maximum size of the
> > contents of each text field in my database?
> >
> > I'm using Access 2003.
> >
> > Thanks,
> > Pat 
> 
> 
> 
0
Reply Utf 11/6/2007 4:49:06 PM

2 Replies
1525 Views

(page loaded in 0.07 seconds)


Reply: