Query truncating memo field

[Jordan]

I'm having a big problem where it seems that all of my
queries are truncating one particular field of format
MEMO at exactly 255 characters. Why is this happeneing
and is there anyway to change it, or get around it so
that all characters in a memo field show in the query?

 

[John Spencer (MVP)]

If it is ALL your queries then I'm not sure what is happening.

Crosstab queries, summary queries, Union queries, and Queries that use Distinct
or DistinctRow will all truncate a memo field to 255 characters so Access can
perform the required functionality of eliminating duplicates.

Also, if you have specified a format in the field's Format property, I believe
that will truncate the data that is displayed.

But I cannot identify any reason for all queries to do this.

Dumb question: Are you sure the field is defined as a memo field and not a
string field with 255 characters? Do you see all the characters when you look
at the table in table in DataSheet View?

 

[Van T. Dinh]

*Genrally*, if you use ORDER BY / GROUP BY or similar,
Access will truncate Memo Field values to max 255 chars.

Post details of your Tables, SQL String of your Query,
whether you need the Query to be updateable or not and
someone may come up with suggestions/solutions.

HTH
Van T. Dinh
MVP (Access)

 

[Jordan]

I figured out that one of two things I'm doing in the
queries is causing it: 1) one of my queries is a summary
query which is the basis for several other queries, and
2) I have the Unique Value property set to YES on most of
my queries, and that seems to cause it. So basically I
can't use the Unique Value property in any query that I'm
using this particular Memo field, and I have to go back
and change my queries around to accomodate that. Not a
big deal. Thanks guys.

 

[Van T. Dinh]

True. Unique value has to compare the values and therefore Memo Field
values are truncated.

 

[Ken Snell]

I usually use First as the choice for memo fields.

Call me slow, but how should I pull my memo field into a query? I thought

"Group By" was the standard generic. ??? Ach!

 

[Ken Snell]

By first, I mean in place of the group by. If you tried using First in place
of the memo field's fieldname, then yes you'd get the parameter prompt.

--

Ken Snell
<MS ACCESS MVP>

I tried First, but then it prompted for criteria when the query was used

for a report. I found that since I didn't need the Total options, I just
turned them off and it stopped truncating. Silly me.