Memo field truncates

[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

 

[Linda Burnside]

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

 

[Ken Snell [MVP]]

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>

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

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

 

[Jake]

Thanks so much to both of you for responding! I implemented the First
aggregate function and it works fine
Jake

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>

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

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