Memo field truncates

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

Hi,
The following SQL statement works to extract the records I need and uses a
Max statement to extract the maximum value from the Amndt No field:

SELECT tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) AS
[MaxOfAmndt No], tblFAR25AmndtList.strFAR25para, tblFAR25AmndtZero.Rule
FROM tblFAR25AmndtList INNER JOIN tblFAR25AmndtZero ON
tblFAR25AmndtList.strFAR25para = tblFAR25AmndtZero.strFAR25para
GROUP BY tblFAR25AmndtZero.Tencount, tblFAR25AmndtList.strFAR25para,
tblFAR25AmndtZero.Rule
ORDER BY tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) DESC;

But for some odd reason it truncates the Rule memo field. Is there a way to
prevent this?
Thanks,
Jake
 
It is a known issue with Access reports that when you use GROUP on a query
which contains a memo field, the memo field will be truncated. A work
around might be to build a temporary table which stores the result of this
query. I'm not sure if it will work (the memo field might get truncated in
this process too) but seems worth a shot.

Linda
 
One workaround is to use First aggregate function on the memo field, not
Group By:

SELECT tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) AS
[MaxOfAmndt No], tblFAR25AmndtList.strFAR25para,
First(tblFAR25AmndtZero.Rule)
AS MemoFieldRule
FROM tblFAR25AmndtList INNER JOIN tblFAR25AmndtZero ON
tblFAR25AmndtList.strFAR25para = tblFAR25AmndtZero.strFAR25para
GROUP BY tblFAR25AmndtZero.Tencount, tblFAR25AmndtList.strFAR25para
ORDER BY tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No])
DESC;

--

Ken Snell
<MS ACCESS MVP>

Linda Burnside said:
It is a known issue with Access reports that when you use GROUP on a query
which contains a memo field, the memo field will be truncated. A work
around might be to build a temporary table which stores the result of this
query. I'm not sure if it will work (the memo field might get truncated
in this process too) but seems worth a shot.

Linda

Jake said:
Hi,
The following SQL statement works to extract the records I need and uses
a Max statement to extract the maximum value from the Amndt No field:

SELECT tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) AS
[MaxOfAmndt No], tblFAR25AmndtList.strFAR25para, tblFAR25AmndtZero.Rule
FROM tblFAR25AmndtList INNER JOIN tblFAR25AmndtZero ON
tblFAR25AmndtList.strFAR25para = tblFAR25AmndtZero.strFAR25para
GROUP BY tblFAR25AmndtZero.Tencount, tblFAR25AmndtList.strFAR25para,
tblFAR25AmndtZero.Rule
ORDER BY tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No])
DESC;

But for some odd reason it truncates the Rule memo field. Is there a way
to prevent this?
Thanks,
Jake
Click to expand...
Click to expand...
 
Thanks so much to both of you for responding! I implemented the First
aggregate function and it works fine
Jake
Ken Snell said:
One workaround is to use First aggregate function on the memo field, not
Group By:

SELECT tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) AS
[MaxOfAmndt No], tblFAR25AmndtList.strFAR25para,
First(tblFAR25AmndtZero.Rule)
AS MemoFieldRule
FROM tblFAR25AmndtList INNER JOIN tblFAR25AmndtZero ON
tblFAR25AmndtList.strFAR25para = tblFAR25AmndtZero.strFAR25para
GROUP BY tblFAR25AmndtZero.Tencount, tblFAR25AmndtList.strFAR25para
ORDER BY tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No])
DESC;

--

Ken Snell
<MS ACCESS MVP>

Linda Burnside said:
It is a known issue with Access reports that when you use GROUP on a
query which contains a memo field, the memo field will be truncated. A
work around might be to build a temporary table which stores the result
of this query. I'm not sure if it will work (the memo field might get
truncated in this process too) but seems worth a shot.

Linda

Jake said:
Hi,
The following SQL statement works to extract the records I need and uses
a Max statement to extract the maximum value from the Amndt No field:

SELECT tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No]) AS
[MaxOfAmndt No], tblFAR25AmndtList.strFAR25para, tblFAR25AmndtZero.Rule
FROM tblFAR25AmndtList INNER JOIN tblFAR25AmndtZero ON
tblFAR25AmndtList.strFAR25para = tblFAR25AmndtZero.strFAR25para
GROUP BY tblFAR25AmndtZero.Tencount, tblFAR25AmndtList.strFAR25para,
tblFAR25AmndtZero.Rule
ORDER BY tblFAR25AmndtZero.Tencount, Max(tblFAR25AmndtList.[Amndt No])
DESC;

But for some odd reason it truncates the Rule memo field. Is there a
way to prevent this?
Thanks,
Jake
Click to expand...
Click to expand...
Click to expand...
 
Back
Top