Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2010 - Difference between Text and Memo

Posted on 2011-10-06
11
Medium Priority
?
4,465 Views
Last Modified: 2012-08-14
In addition to the storage capacity, are there any differences between the "memo" and "text" data type?
0
Comment
Question by:brothertruffle880
11 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36925991
Storage is the big one.

Memo has some query limitations when used in the GROUP BY or ORDER BY clauses, or use Select Distinct
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36925992
None..
memo data type is an extended version of Text data type fields.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1600 total points
ID: 36925994

A Memo data type is stored somewhat differently that Text data type, but that is all transparent.

Memo.
"Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)"

Text:
"Up to 255 characters or the length set by the FieldSize property, whichever is less. Microsoft Access does not reserve space for unused portions of a text field."

mx
1
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 36926012
Off the top of my head.

Memo fields can be configured to display RichText, text fields cannot (2007 and beyond, don't recall about earlier versions)

Memo fields get truncated (I think it is at 255 char, but am not certain) when you use them in a Group By clause.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1600 total points
ID: 36926024
This might be of interest to you:

Truncation of Memo fields
http://www.allenbrowne.com/ser-63.html

Grouping by Memo field yields garbage
http://www.allenbrowne.com/bug-18.html

So, in reality there are some significant differences.  What I said 'transparent', I meant regarding the storage aspect.

mx

0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1600 total points
ID: 36926032
Also, only the first 255 characters of a Memo field can be Indexed.
0
 
LVL 59
ID: 36926047
Believe the ability to sort on a memo field has been added as well (first 255 characters), so there is very little difference.

  As the others have said though, one of the differences is how they are stored.  Text fields are saved with the "fixed" portion of the record (stored with the rest of the fields in the record).

  Memo and OLE fields are stored on long value pages in a chain.  While that's really not a problem, the kicker is that LVP's are always locked by page and fields from multiple records can be on one page.  So even if you have record level locking, if you have memo fields in the table, your going to have problems.

Jim.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1600 total points
ID: 36926061
Form
http://office.microsoft.com/en-us/access-help/about-differences-between-data-types-in-an-access-database-and-access-project-HP005274573.aspx

"HideChoosing between a Text or Memo field

Microsoft Access provides two field data types to store data with text or combinations of text and numbers: Text and Memo.

Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. The FieldSize property controls the maximum number of characters that can be entered in a Text field.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.

Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

You can sort or group on a Text field or a Memo field, but Access only uses the first 255 characters when you sort or group on a Memo field."

mx
0
 
LVL 75
ID: 36926124
In summary, I guess when you get right down to it, there are some significant differences.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36926140
One more quick one:

Memo: No Input Mask
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36926219
Are you trying to decide between the two?

Many people hear the 255 character limit of a text field and think that (compared to a memo field, 65k), that it is "Small".

Actually 255 characters can end up being 4 sentences of text, ....more that enough for most "Free Text" field needs.

In what *I* do, I rarely need any thing with RTF, HTML, ...etc
...and all the complexity this sometimes entails.

Nor do I feel much like worrying about all the other issues (that the previous experts mentioned) with memo fields...

Again, just my 2c...

;-)

JeffCoachman
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question