Unusual reason for Memo truncation

[CP]

I've got a strange situation with a truncated memo field that doesn't
have any of the usual causes so I'm wondering if anyone can explain
it.

This SQL (with Group By) doesn't result in truncation
(TransactionComment is the memo field in question):
SELECT qspt_Ta_CLSSYSVI.TA_IFMS_ID,
qspt_Ta_CLSSYSVI.TransactionComment
FROM qspt_Ta_CLSSYSVI
GROUP BY qspt_Ta_CLSSYSVI.TA_IFMS_ID,
qspt_Ta_CLSSYSVI.TransactionComment;

But this one does:
SELECT qspt_Ta_CLSSYSVI.TA_IFMS_ID,
qspt_Ta_CLSSYSVI.TransactionComment, IIf([TotalPrice] Is Null,"0",
[totalprice]) AS TotPrice
FROM qspt_Ta_CLSSYSVI
GROUP BY qspt_Ta_CLSSYSVI.TA_IFMS_ID,
qspt_Ta_CLSSYSVI.TransactionComment, IIf([TotalPrice] Is Null,"0",
[totalprice]);

(The IIF(TotalPrice...) function was added to make Null values show as
0)

I read a post that said: "The query truncates the text if it contains
DISTINCT or GROUP BY or
something that involves comparing the values on each row."

And another post said: Do not use and functions in any columns (Left
(), Mid(), InStr(), IIF(), etc.)

I guess my situation falls into the latter BUT I'd really like an
explanation of WHY? (And why did I have such a hard time finding
these two posts? Early on, I found (and appreciated) Allen Browne's
helpful page about Truncation but even HE doesn't include this
situation.)

Thanks in advance for any and all information. Carol.

 

[Danny Lesandrini]

Carol:

Was it my post that said you shouldn't use functions in the query? I had written such a post, after reviewing other peoples
comments and doing some extensive testing. Maybe someone else has an answer, but I never could figure it out. It's something
in the "black box" processing behind Access that simply cuts the field off. You simply can't get around it.

What I'm saying is, you can't beat them, so join them.

 

[CP]

Hi, Danny. I honestly can't remember if it was a post of yours that I
was "quoting." But I agree with you that there are just some
inexplicable things that happen in Access (hopefully they made sense
to the initial programmers!) and you just have to figure out a
workaround.

Thanks. Carol.