Unmatched Query and Memo Field

[Robert]

I have created an unmatched query by using the wizard in Access 97.
However, I find that it doesn't show the difference of a text field with
size 255. Is it correct OR I have missed something ? Is it necessary for
me to map the fields one by one (Actually, they are the same table but one
is 1 month before) ?

Moreover, one of the text field with size 255 is not sufficient and I will
change it to Memo field. What is the difference between a text and memo
field ?

Thanks

 

[Arvin Meyer [MVP]]

Yes, you'll need to map dissimilar named fields individually. You cannot map
a text field to a memo field. The difference is that a text field stores the
data in the same table and has a limit of 255 characters; a memo field
stored the data in another location and has a limit of 64K (or 1 GB when
entering data programmatically). Text fields can be indexed and sorted, memo
fields cannot.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J Steele]

If you're trying to compare records, yes, you need to map the fields one by
one.

Regarding Memo fields vs. Text fields, from the Help file:

"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. To control the maximum number of
characters that can be entered in a Text field, set the FieldSize property.

"Use the Memo data type if you need to store more than 255 characters. A
Memo field can store up to 64,000 characters. Memo fields can't be indexed
or sorted. If you want to store formatted text or long documents, you should
create an OLE field instead of a Memo field."

What it doesn't mention is that you can't create indexes on Memo fields.

 

[Robert]

Dear Arvin,

Thank you for your advice.

However, as mentioned in previously, both of the tables structure are
exactly the same. There is no dissimilar named fields to match. It is
strange for the query not returning the unmatched rows.

Since the comment > 255 characters, I think that the only way is to use a
memo field (Maybe of size 400 characters). I believe that the memo field is
stored in the same table OR there is a linked table stored the memo field ?

Robert

 

[Arvin Meyer [MVP]]

The memo data is displayed in the same table but not stored there. Instead,
a 14 byte pointer is actually stored in the table. In the event that the
actual data stored in the memo field is less than 14 bytes, it is possible
to store it in the table, but usually it's the pointer that's stored.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Robert]

Dear Arvin,

Thank you for your advice.
I would like to know
1) In my case, if I change the CHAR field to MEMO with 400 characters, will
the data be stored in the table ?
2) If NOT, I believe that it still stored in the database ? This is because
I have to send the database to someone in another company.

Thanking you in anticipation.

Robert