C
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.
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.