Link to home
Start Free TrialLog in
Avatar of psonix
psonix

asked on

Memo Field being truncated by Group By

I am sure that there is a better way to write this query, but I don't know what it is.

SELECT  qry_SearchUnion.Problem, Count(qry_SearchUnion.RecapID) AS CountOfRecapID, qry_SearchUnion.Job_Name, qry_SearchUnion.Date, qry_SearchUnion.Error_Code, qry_SearchUnion.Solution, qry_SearchUnion.Primary, qry_SearchUnion.Secondary, qry_SearchUnion.External_People, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_Ticket, qry_SearchUnion.ID, qry_SearchUnion.[Open Time], qry_SearchUnion.[Update Time], qry_SearchUnion.[Alert Status], qry_SearchUnion.Severity, qry_SearchUnion.[CI Severity], qry_SearchUnion.Category, qry_SearchUnion.Title
FROM qry_SearchUnion
GROUP BY qry_SearchUnion.Problem, qry_SearchUnion.Job_Name, qry_SearchUnion.Date, qry_SearchUnion.Error_Code, qry_SearchUnion.Solution, qry_SearchUnion.Primary, qry_SearchUnion.Secondary, qry_SearchUnion.External_People, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_Ticket, qry_SearchUnion.ID, qry_SearchUnion.[Open Time], qry_SearchUnion.[Update Time], qry_SearchUnion.[Alert Status], qry_SearchUnion.Severity, qry_SearchUnion.[CI Severity], qry_SearchUnion.Category, qry_SearchUnion.Title
HAVING (((Count(qry_SearchUnion.RecapID))=8))
ORDER BY Count(qry_SearchUnion.RecapID) DESC;

The memo fields qry_SearchUnion.Problem and qry_SearchUnion.Solution get truncated to 255 characters because of the group by. To test this I copied the query, removed the aggregates and the memos worked on a form. I need to have the Count(qry_SearchUnion.RecapID) AS CountOfRecapID in there, but the other group bys are there just so it wont throw the aggregates error.

I saw this touched on : https://www.experts-exchange.com/questions/20948020/MS-Access-2000-query-memo-field-truncated-to-255-characters-because-of-GROUP-BY-clause.html?query=query+truncates+memo+field&clearTAFilter=true

but I couldn't get it to work for me, any advice?

I am using Access 2003.
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of psonix
psonix

ASKER

tried both of those and they don't work.
Avatar of psonix

ASKER

I tried the second link that creates the temp table in the subquery, and that will get the memo fields through, but I can't find a way to get the count function involved
have you tried expanding your OrderBy clause to?
ORDER BY Count(qry_SearchUnion.RecapID) DESC, qry_SearchUnion.Problem, qry_SearchUnion.Solution;
Avatar of psonix

ASKER

I ended up using the second link and having to use it as a base to create a work around off of.
OK glad you're winning.