How to determine maximum size of field contents

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
Utf
11/6/2007 4:06:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2899 Views

Similar Articles

[PageSpeed] 28

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
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
Utf
11/6/2007 4:49:06 PM
Reply:

Similar Artilces:

Excel found unreadable content 11-17-09
I recently was upgraded to Excel 2007, when I try to open my spreadsheets that have large pivot tables I get the Excel found unreadable content error I can click on yes to recover however my pivot tables are no longer pivot tables they are just the values in a cell. If I open up the spreadsheet in Excel 2003, no error and the pivot tables are still functioning. Any ideas what I can do? ...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

How can I default a lookup field to a particular value.
How can I default a lookup field to a certain value, but still allow the user to modify the field if necessary? Thanks, Bob Johnson you mean a drop down or picklist? "Bob" <rjohnson@reveregroup.com> wrote in message news:1130947400.655495.115390@f14g2000cwb.googlegroups.com... > How can I default a lookup field to a certain value, but still allow > the user to modify the field if necessary? > > Thanks, > > Bob Johnson > i have the same question in crm 3.0. i want a default value in a lookup filed. i also want to create a lookup field by my self....

Return Maximum value
Hi Looking to find a formula that will calculate a maximum bid figure from an array. Example data. Products Bidder Amount Product 1 John �50 Product 2 David �50 Product 1 William �55 Product 1 Jill �45 Product 3 Tom �60 Product 3 Gwen �30 So when I put s table together of all Product I get the highest bidders for each e.g. Products Bidder Amount Product 1 William �55 Product 2 David �50 Product 3 Tom �60 Any ideas? Thanks 1)List your products from E1:E3 2)Put this formula in Cell D1: =MAX(IF($A...

need to concatenate field
Hi, I need to concatenate a field, and sum another field, grouping the first field. Acct legalDescrip acres 77 Parcel-1Text 10 77 Parcel-2Text 20 77 Parcel-3Text 5 80 Parcel-1Text 11 86 Parcel-1Text 12 86 Parcel-2Text 12 The result I need: Acct legalDescrip acres 77 Parcel-1Text;Parcel-2Text;Parcel3Text 35 80 Parcel-1Text 5 86 Parcel-1Text;Parcel-2Text 24 For some records, the total of the LegalDescription...

Lost partial header content
When viewing in "print layout" (MS 2007) mode I can only see a part of the actual content of the header. PS: When printing the document the complete information is there however. How do I get to view the complete info in the printer as it prints??? :) Print preview? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<&...

Maximum Database Size Reached
All I am running MS Exchange 5.5 sp4. My Information Store has just shut down due to the Database size reaching its maximum. I am about to follow the Knowledge Base article 185457 which explains that i need to defragment the database to try and reclaim some free space. I will run eseutil /d /ispriv Is there anything else that I should look out for? At the moment I am running an offline Backup in case my defrag goes wrong. Any help/sugestions greatly appreciated. Kevan After you start IS make sure that IS Maintenance is set to always, Deleted Items Retention is set to 0 days, conv...

Calendar control on protected form with multiple date fields
How do a reference the formfield on a protected form to insert a date from a datepicker? I have 3 date fields, it seems I can insert a date based on the field selected i.e. selection.formfields("x")=calendar1.value Any help is appreciated. thanks -- See http://www.gmayor.com/popup_calendar.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

field chooser won't keep
Outlook 2003 Windows XP I choose the fields I want for my various folders (inbox, sent, deleted, etc) But every so often, my selection goes away (for example, my send box insists are saying who the emails are FROM instead of TO, I know that I sent them).. Any suggestions? -- Meg "Our lives begin to end the day we become silent about things that matter" - MLK the views are getting reset or otherwise messing up. Use the method at http://www.outlook-tips.net/howto/grouping.htm to reset the views and then customize the views. -- Diane Poremsky [MVP - Outlook] Author, Teac...

Page size for the background= Page size of the first page of the drawing
Hi all How do I set up a formula to have the page size for the background page=page size of the first page of the drawing ? I know I have to set up the "PageWidth" to somthing like this: PageWidth=Pages[1].PageWidth but it doesn't work. I am getting an error. Any clue ? I have to mention that my idea is to set a template for a page which has the size the same as the drawing has. The problem is that I need to locate let's say the logo of the company always down in the left corner of the page.The page's size could be changes according with the size of the drawing so th...

Date & Time Field
I am running Excel 2003. I have a field that combines both date and time (MM/DD/YYYY HH:MM:SS AM or PM). I would like to know if there is a way break this field into a date field (MM/DD/YYYY) and a time field (HH:MM). Thanks. Hi Diane For Date = INT(A1) format as a suitable Date format For Time =MOD(A1,1) format as hh:mm -- Regards Roger Govier "Diane Walker" <ett9300@yahoo.com> wrote in message news:OpkM6IXFGHA.2652@tk2msftngp13.phx.gbl... >I am running Excel 2003. I have a field that combines both date and >time (MM/DD/YYYY HH:MM:SS AM or PM). I wou...

Is there any limit for the size of std:string???
Hi, I made some code that compresses video data. And It lookes OK if I compress data very much (few bytes) and not so well if data is not compressed at all (many bytes). I have there a lot of variables but I think there is something about std:string. Is there any limit for the size of std:string??? Your question vary vague, but the answer to the question "Is there any limit for the size of std:string?" is (as far as I know) computer memory. My concern is that std:string is not a very good medium for holding video data, since a 0 will terminate the string! And I am pretty sure th...

Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The spreadsheets currently have 100 columns (from 1-100). From there i have to reduce the number of columns set to 8 columns. E.g: 1 2 3 4 5 6 7 8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it goes to the 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 How do i do that without altering the order of the value of the cell? Let me make sure we understand the question. You have ...

Posting to Public Folder-VIEW--the FROM field
When I post to a public folder, and click VIEW--the FROM field is grayed out. This worked in Outlook 2000. Are there any work-arounds? ...

Window Size
I'm using a report utilities to export a report form to a word document. When I press the export button on the form, it changes the window size to smaller than the allowed maximum. I don't want that report form to change in size. Is there a way to stop this? Thanks in advance for the help. ...

PST file size is bigger than its actual folder's size
Hi all, I've recently noticed a problem in my outlook.pst file, when I choos the properties of the personal folders and check the folder size, th total file size (including subtitles) is 10MB, but when I check th outlook.pst on my HDD, the file size is 35MB. I've noticed that outlook is getting slower day after day. anyone can help please? thnx Joh ----------------------------------------------- ~~ Message posted from http://www.OutlookForum.com ~~ View and post usenet messages directly from http://www.OutlookForum.com What version of Outlook? If it is XP, you may need to com...

Add dashes to field....
I have a table titled Internet Usocs 1-11-10 that I am trying to update the field titled Primary Phone. I need to make the Primary Phone field to included dashes (###-###-####). I used the following in an update query: Format("Primary Phone","###-###-####") I also used an SQL statment below: UPDATE Internet Usocs 1-11-10 SET Internet Usocs 1-11-10.Primary Phone = Format([Primary Phone],"@@@-@@@-@@@@") WHERE (((Len([Primary Phone]))=10)); Each time I get an error stating: Microsoft Office Access didn't update 5626 fields(s) due to a type...

Lookup fields referring to tbls w/o relationship
I created a db and in reading now about db structure, I've seen a lot of mention of relationships. My db started out very simple with the tables that I used for relationships all being one-to-one and now I'm considering adding to it. In it, however, I also have some tables that I used (not w/ the Lookup Wizard but on my own) simply as lookup - populating cbos. I didn't seem to need to set relationships with these though for it to work and work well. Why didn't it require a relationship and what are the pros & cons of setting them up this way vs. w/ a relat...

XML Serialization : Error reflecting field.... #2
I have to deserialize an XML document to objects and then serialize it back to XML to pass to the stored proc. I am attaching partial code. After this , I also have to serialize Here is the XML : <data> <orders> <order> <order_id>0</order_id> <issue_id>4460</issue_id> <action>add</action> </order> </orders> </data> This is the main.cs file where I call the Deserializer public class Main { public void AcceptOrdersDom( string strInputXML ) { OrderRootDeSerialize ordRoot =(OrderRootDeSerializ...

Exchange 2000 DB size
I ran into my 16 GB limit on an E2000 server (part of SBS2000) yesterday. So, I did the temporary increase, offline defrag & mounted the store. The actual DB size when done was 12-1/2 GB with a streaming file of roughly 3-1/4 GB. That is, the size hardly changed at all. I then had several major users archive old messages into PST files (probably about 3 GB), let it runs its maintenance overnight (online defrag & backup to tape, including commit & flush logs), and then re-ran offline defrag this morning. The EDB file size is still about the same, and the online defrag (1221 ...

Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending...

Concatenate with Javascript if 3 fields are present
I have 3 fields in a form that I need to concatenate into a string to populate a new field called Test ID, the format ulfor test id should equal LNAME_DOB_TestDate. Patient Name (format is LNAME, FNAME) DOB TestDate There should always be a comma between the LNAME, FNAME field. I need to parse out the LName to the comma to create the string LNAME_DOB_TestDate. This should only happen when all 3 fields are present. Any help appreciated. Thanks. ...

Blank To and From Fields
We are on Exchange 2003 and my folks are receiving e-mail messages where both TO and FROM fields are blank. Is there a way to block messages when both TO and FROM are Blank? This is what the header looks like.... X-Comment: Sending client does not conform to RFC822 minimum requirements X-Comment: Date has been added by Maillennium Received: from X.X.X.X (unknown[X.X.X.X](misconfigured sender)) Thanks! -- NC Beach Bum And they are SPAM, right? Exchange 2003 allows you to filter messages that have a Blank sender. If you want to do this, you must enable it in 2 places. First, under Glo...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...

GP 8.0 and SQL 2000 Optimal File Size
I have questions about SQL Server 2000 databases with Great Plains 8.0 and file size. After the upgrade to 8.0 our Company database has ballooned to a whopping 9 GB (including the transaction log). It was 7 GB before the upgrade. It seems really large to me and was wondering what the recommended settings were for the SQL databases with Great Plains. I have looked in the Knowledgebase and haven't found anything to give me good answers. Here are the specs on the Company database: Database Size = 7,184 MB, Space Used = 4,655 MB Log Size = 2,162 MB, Space Used = 76.99 MB Auto G...