Memo field in report is limited 1

[Dophia]

Hi Everyone I am using Access 2003 and I have a query within a query to print a report. From researching other posts, it seems that this is the reason the memo field is limited to the size of a text field. But I don't know how to fix my query so that the memo field is complete.

Here is my query.

SELECT QryAssessment_for_Latest_Pt2.Pound_Sheet_No, Max(QryAssessment_for_Latest_Pt2.MaxOfAssessID) AS MaxOfMaxOfAssessID1, tblAssessment_Details.AssessDate, tblAnimals.Arrival_Date, tblAssessment_Details.AssessTypeCats, tblAssessment_Details.AssessTypeDogs, tblAssessment_Details.Friendly, tblAssessment_Details.Timid, tblAssessment_Details.Recommendation
FROM (tblAnimals INNER JOIN QryAssessment_for_Latest_Pt2 ON tblAnimals.Pound_Sheet_No = QryAssessment_for_Latest_Pt2.Pound_Sheet_No) INNER JOIN tblAssessment_Details ON QryAssessment_for_Latest_Pt2.MaxOfAssessID = tblAssessment_Details.AssessID
GROUP BY QryAssessment_for_Latest_Pt2.Pound_Sheet_No, tblAssessment_Details.AssessDate, tblAnimals.Arrival_Date, tblAssessment_Details.AssessTypeCats, tblAssessment_Details.AssessTypeDogs, tblAssessment_Details.Friendly, tblAssessment_Details.Timid, tblAssessment_Details.Recommendation;

The "recommendation" field is a memo.

Any help would be appreciated

Thank you,
Sophia

 

[dhookom]

You must remove the GROUP BY on the Recommendation field. Try:

SELECT QryAssessment_for_Latest_Pt2.Pound_Sheet_No, 
  Max(QryAssessment_for_Latest_Pt2.MaxOfAssessID) AS MaxOfMaxOfAssessID1,
  tblAssessment_Details.AssessDate, tblAnimals.Arrival_Date,
  tblAssessment_Details.AssessTypeCats, tblAssessment_Details.AssessTypeDogs,
  tblAssessment_Details.Friendly, tblAssessment_Details.Timid, First(tblAssessment_Details.Recommendation)  as Recommend
FROM (tblAnimals INNER JOIN QryAssessment_for_Latest_Pt2 ON tblAnimals.Pound_Sheet_No = QryAssessment_for_Latest_Pt2.Pound_Sheet_No)
 INNER JOIN tblAssessment_Details ON QryAssessment_for_Latest_Pt2.MaxOfAssessID = tblAssessment_Details.AssessID
GROUP BY QryAssessment_for_Latest_Pt2.Pound_Sheet_No, tblAssessment_Details.AssessDate, tblAnimals.Arrival_Date, tblAssessment_Details.AssessTypeCats, tblAssessment_Details.AssessTypeDogs, tblAssessment_Details.Friendly, tblAssessment_Details.Timid;

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks for your help Duane. But now it asks for the "Enter paramater value" Recommendation.

I don't understand why.

 

[dhookom]

I expect the issue is that there is no field/column in the query. The query has to rename the column since it is not in the GROUP BY. Change the field name in the report.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thank you Duane, it works perfectly!