memo field in select query truncated to 255

[Chris]

my query has some inner joins, otherwise, it's a basic
select query. The most important field in the result is a
Memo field, and the results are truncating to 255! This
is mission critical. A major report depends on this
query, and i convinced people that Access was the solution.

Help, please!

chris

 

[Allen Browne]

Two possible causes:

1. Grouping on memo
If this this is a Totals query (has a GROUP BY clause), change the Total row
under your memo field to:
First
Grouping on a memo field causes it to truncate. Note that you will also need
to change the Control Source of the text box on the report to something like
FirstOfMyMemo.

2. Formatting of memo
Open the table in design view, and remove any characters in the Format
property of the memo field. Also remove anything in the Format property of
the text box on the report.
Typically this occurs if you are trying to convert the case of the field,
and the formatting truncates the memo.

 

[chris]

Allen,

Thanks for your response.

Unfortunately, neither 1 nor 2 apply.

Below is my query (note, my memo field is
HIRO2_SESSION_DATA.COMMENTS):

++++++

SELECT DISTINCT HIRO2_SESSION_DATA.SESSION_DATA_ID,
HIRO2_SESSION_DATA.USER_ID, HIRO2_SESSION_DATA.COMMENTS,
HIRO2_USER.LASTNAME, HIRO2_USER.FIRSTNAME,
tmp_AllItems.CD_ID, HIRO2_PROJECT.PROJECT_ID,
HIRO2_PROJECT.NAME, HIRO2_SESSION_DATA.GIT_ID
FROM ((HIRO2_SESSION_DATA INNER JOIN tmp_AllItems ON
HIRO2_SESSION_DATA.GIT_ID = tmp_AllItems.GIT_ID) INNER
JOIN HIRO2_USER ON HIRO2_SESSION_DATA.USER_ID =
HIRO2_USER.USER_ID) INNER JOIN HIRO2_PROJECT ON
HIRO2_SESSION_DATA.PROJECT_ID = HIRO2_PROJECT.PROJECT_ID
WHERE (((HIRO2_SESSION_DATA.HAS_COMMENT)=1) AND ((Len(Trim
([COMMENTS])))<>"0"));

++++++

The following query gives me the full content of the memo
field,:

++++++

SELECT HIRO2_SESSION_DATA.COMMENTS
FROM HIRO2_SESSION_DATA
WHERE (((Len(Trim([COMMENTS])))<>0));

++++++

so, there is something about the first query......

thanks in advance,

 

[Allen Browne]

Try dropping "DISTINCT".

It has the same effect of forcing Access to compare the memo fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

Thanks for your response.

Unfortunately, neither 1 nor 2 apply.

Below is my query (note, my memo field is
HIRO2_SESSION_DATA.COMMENTS):

++++++

SELECT DISTINCT HIRO2_SESSION_DATA.SESSION_DATA_ID,
HIRO2_SESSION_DATA.USER_ID, HIRO2_SESSION_DATA.COMMENTS,
HIRO2_USER.LASTNAME, HIRO2_USER.FIRSTNAME,
tmp_AllItems.CD_ID, HIRO2_PROJECT.PROJECT_ID,
HIRO2_PROJECT.NAME, HIRO2_SESSION_DATA.GIT_ID
FROM ((HIRO2_SESSION_DATA INNER JOIN tmp_AllItems ON
HIRO2_SESSION_DATA.GIT_ID = tmp_AllItems.GIT_ID) INNER
JOIN HIRO2_USER ON HIRO2_SESSION_DATA.USER_ID =
HIRO2_USER.USER_ID) INNER JOIN HIRO2_PROJECT ON
HIRO2_SESSION_DATA.PROJECT_ID = HIRO2_PROJECT.PROJECT_ID
WHERE (((HIRO2_SESSION_DATA.HAS_COMMENT)=1) AND ((Len(Trim
([COMMENTS])))<>"0"));

++++++

The following query gives me the full content of the memo
field,:

++++++

SELECT HIRO2_SESSION_DATA.COMMENTS
FROM HIRO2_SESSION_DATA
WHERE (((Len(Trim([COMMENTS])))<>0));

++++++

so, there is something about the first query......

thanks in advance,

-----Original Message-----
Two possible causes:

1. Grouping on memo
If this this is a Totals query (has a GROUP BY clause), change the Total row
under your memo field to:
First
Grouping on a memo field causes it to truncate. Note that you will also need
to change the Control Source of the text box on the report to something like
FirstOfMyMemo.

2. Formatting of memo
Open the table in design view, and remove any characters in the Format
property of the memo field. Also remove anything in the Format property of
the text box on the report.
Typically this occurs if you are trying to convert the case of the field,
and the formatting truncates the memo.

solution.