Text Field truncation in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that gets a 'description' field from a query. The query gets
this field from a table that defines it as a 'memo' field. When I run the
report the 'description' field is being truncated after 255 characters and I
have traced this back to the query itself.
The 'description' object on the report has its 'Can grow' property set to
yes as does the Detail section of the report where it appears. The problem
appears to be in the query itself - it does not allow more than 255
characters in a cell.
How do I get around this problem?
 
It really helps in a queries NG to at times post the SQL of the query. This
is one of those times. You might want to search out and remove any phrases
like "DISTINT" or "GROUP BY". If this doesn't work, then come back with your
SQL.
 
Duane,
Because of the nature of the report it's not possible to strip out 'Group
By'; there are no 'Distinct' clauses.
Here's the SQL - it's rather complicated: the fields that's being truncated
are T_Case.CaseDescription and T_Case.CaseStatus.

SELECT T_Company.CompanyAbbreviation, T_Case.CaseOpenedDT,
T_Case.SettlementDecisionDT, T_Case.SuitFiledDT, T_Case.ServedDT,
T_Case.CaseShortName, T_State_Code.StateName, T_Case.CourtName,
T_LOB.LOBDescription, T_InsideCounsel.InsideCounselInitials,
T_Case.PlantiffName, First(T_Policy.PolicyID) AS FirstOfPolicyID1,
First(T_Policy.MonthlyIndemnityAmount) AS FirstOfMonthlyIndemnityAmount1,
First(T_Policy.PVFutureBenefitAmount) AS FirstOfPVFutureBenefitAmount1,
T_Case.CaseDescription, T_Case.CaseStatus, T_Case.CalendarItems,
qryDefPlaintCounsel.[Plaintiff Outside Counsel],
qryDefPlaintCounsel.[Plaintiff Opposing Counsel],
qryDefPlaintCounsel.[Defendant Opposing Counsel],
qryDefPlaintCounsel.[Defendant Outside Counsel],
T_Case.BenefitAmountUnpaidPriorToSettlement,
First(T_Policy.ClaimBenefitPaidToDTAmount) AS
FirstOfClaimBenefitPaidToDTAmount, T_Case.DefendantName, T_Case.PlantiffName
FROM (T_Company INNER JOIN (T_CaseStatus INNER JOIN (T_LOB INNER JOIN
((((T_InsideCounsel INNER JOIN T_Case ON T_InsideCounsel.InsideCounselID =
T_Case.InsideCounselID) LEFT JOIN T_Case_Policy ON T_Case.CaseID =
T_Case_Policy.CaseID) LEFT JOIN T_Policy ON T_Case_Policy.PolicyID =
T_Policy.PolicyID) INNER JOIN T_State_Code ON T_Case.StateSuitID =
T_State_Code.StateID) ON T_LOB.LOBID = T_Case.LOBID) ON
T_CaseStatus.CaseStatusID = T_Case.CaseStatusID) ON T_Company.CompanyID =
T_Case.CompanyID) LEFT JOIN qryDefPlaintCounsel ON T_Case.CaseID =
qryDefPlaintCounsel.CaseID
GROUP BY T_Company.CompanyAbbreviation, T_Case.CaseOpenedDT,
T_Case.SettlementDecisionDT, T_Case.SuitFiledDT, T_Case.ServedDT,
T_Case.CaseShortName, T_State_Code.StateName, T_Case.CourtName,
T_LOB.LOBDescription, T_InsideCounsel.InsideCounselInitials,
T_Case.PlantiffName, T_Case.CaseDescription, T_Case.CaseStatus,
T_Case.CalendarItems, qryDefPlaintCounsel.[Plaintiff Outside Counsel],
qryDefPlaintCounsel.[Plaintiff Opposing Counsel],
qryDefPlaintCounsel.[Defendant Opposing Counsel],
qryDefPlaintCounsel.[Defendant Outside Counsel],
T_Case.BenefitAmountUnpaidPriorToSettlement, T_Case.DefendantName,
T_Case.PlantiffName
HAVING (((T_Case.CaseOpenedDT)>=[Beginning Date] And
(T_Case.CaseOpenedDT)<=[Ending Date])) OR (((T_Case.SettlementDecisionDT) Is
Null)) OR (((T_Case.SettlementDecisionDT)>=[Beginning Date] And
(T_Case.SettlementDecisionDT)<=[Ending Date]))
ORDER BY T_Company.CompanyAbbreviation, T_Case.CaseShortName;
 
The Group By is causing the truncation. You have at least two options:
1) remove the T_Case table while the query is being grouped by and then add
it back in to the resulting group by
2) using First(T_Case.CaseDescription) rather than Group By
T_Case.CaseDescription

--
Duane Hookom
MS Access MVP


DaveK said:
Duane,
Because of the nature of the report it's not possible to strip out 'Group
By'; there are no 'Distinct' clauses.
Here's the SQL - it's rather complicated: the fields that's being truncated
are T_Case.CaseDescription and T_Case.CaseStatus.

SELECT T_Company.CompanyAbbreviation, T_Case.CaseOpenedDT,
T_Case.SettlementDecisionDT, T_Case.SuitFiledDT, T_Case.ServedDT,
T_Case.CaseShortName, T_State_Code.StateName, T_Case.CourtName,
T_LOB.LOBDescription, T_InsideCounsel.InsideCounselInitials,
T_Case.PlantiffName, First(T_Policy.PolicyID) AS FirstOfPolicyID1,
First(T_Policy.MonthlyIndemnityAmount) AS FirstOfMonthlyIndemnityAmount1,
First(T_Policy.PVFutureBenefitAmount) AS FirstOfPVFutureBenefitAmount1,
T_Case.CaseDescription, T_Case.CaseStatus, T_Case.CalendarItems,
qryDefPlaintCounsel.[Plaintiff Outside Counsel],
qryDefPlaintCounsel.[Plaintiff Opposing Counsel],
qryDefPlaintCounsel.[Defendant Opposing Counsel],
qryDefPlaintCounsel.[Defendant Outside Counsel],
T_Case.BenefitAmountUnpaidPriorToSettlement,
First(T_Policy.ClaimBenefitPaidToDTAmount) AS
FirstOfClaimBenefitPaidToDTAmount, T_Case.DefendantName, T_Case.PlantiffName
FROM (T_Company INNER JOIN (T_CaseStatus INNER JOIN (T_LOB INNER JOIN
((((T_InsideCounsel INNER JOIN T_Case ON T_InsideCounsel.InsideCounselID =
T_Case.InsideCounselID) LEFT JOIN T_Case_Policy ON T_Case.CaseID =
T_Case_Policy.CaseID) LEFT JOIN T_Policy ON T_Case_Policy.PolicyID =
T_Policy.PolicyID) INNER JOIN T_State_Code ON T_Case.StateSuitID =
T_State_Code.StateID) ON T_LOB.LOBID = T_Case.LOBID) ON
T_CaseStatus.CaseStatusID = T_Case.CaseStatusID) ON T_Company.CompanyID =
T_Case.CompanyID) LEFT JOIN qryDefPlaintCounsel ON T_Case.CaseID =
qryDefPlaintCounsel.CaseID
GROUP BY T_Company.CompanyAbbreviation, T_Case.CaseOpenedDT,
T_Case.SettlementDecisionDT, T_Case.SuitFiledDT, T_Case.ServedDT,
T_Case.CaseShortName, T_State_Code.StateName, T_Case.CourtName,
T_LOB.LOBDescription, T_InsideCounsel.InsideCounselInitials,
T_Case.PlantiffName, T_Case.CaseDescription, T_Case.CaseStatus,
T_Case.CalendarItems, qryDefPlaintCounsel.[Plaintiff Outside Counsel],
qryDefPlaintCounsel.[Plaintiff Opposing Counsel],
qryDefPlaintCounsel.[Defendant Opposing Counsel],
qryDefPlaintCounsel.[Defendant Outside Counsel],
T_Case.BenefitAmountUnpaidPriorToSettlement, T_Case.DefendantName,
T_Case.PlantiffName
HAVING (((T_Case.CaseOpenedDT)>=[Beginning Date] And
(T_Case.CaseOpenedDT)<=[Ending Date])) OR (((T_Case.SettlementDecisionDT) Is
Null)) OR (((T_Case.SettlementDecisionDT)>=[Beginning Date] And
(T_Case.SettlementDecisionDT)<=[Ending Date]))
ORDER BY T_Company.CompanyAbbreviation, T_Case.CaseShortName;


Duane Hookom said:
It really helps in a queries NG to at times post the SQL of the query. This
is one of those times. You might want to search out and remove any phrases
like "DISTINT" or "GROUP BY". If this doesn't work, then come back with your
SQL.

--
Duane Hookom
MS Access MVP


query
gets and
I
Click to expand...
Click to expand...
 
Back
Top