MS Access 2k query seems to be casting a MEMO field into a TEXT field

[ozimandius]

The MainTable.Descrption field is a MEMO field but the straight forward query below only returns the first 256 characters from the Description field. Any ideas why?

The MainTable table to Comments table is a 1 to many relationship, and I am trying to capture the count of the Comments for each item in MainTable.

SELECT top 25 MainTable.Description, MainTable.Counter, MainTable.EntryDate, MainTable.URL, MainTable.TagLine, MainTable.Category, MainTable.ImageLink, MainTable.ImagePercentage, MainTable.UserName, MainTable.Aproved, Count(Comments.StoryID) AS CountOfStoryID
FROM MainTable LEFT JOIN Comments ON MainTable.Counter = Comments.StoryID
GROUP BY MainTable.Counter, MainTable.EntryDate, MainTable.URL, MainTable.TagLine, MainTable.Description, MainTable.Category, MainTable.ImageLink, MainTable.ImagePercentage, MainTable.UserName, MainTable.Aproved
ORDER BY MainTable.EntryDate desc, MainTable.Counter, MainTable.URL, MainTable.TagLine, MainTable.Description, MainTable.Category, MainTable.ImageLink, MainTable.ImagePercentage, MainTable.UserName, MainTable.Aproved;

Without the GroupBy part, I get an error saying I need to use "Description as part of an agragate function", but once that's in place it concatonates the Description field. Removing the GroupBy clause and the Count(Comments.StoryID) AS CountOfStoryID gives me the Description field in total. How else can I get all the information in a single query?

 

[ozimandius]

Ok, I probably should have searched these forums a little better before posting...

Solution:
SELECT top 25 MainTable.Counter, MainTable.EntryDate, MainTable.URL, MainTable.TagLine, MainTable.Description, MainTable.Category, MainTable.ImageLink, MainTable.ImagePercentage, MainTable.UserName, MainTable.Aproved, (SELECT Count(Comments.StoryID) FROM Comments WHERE MainTable.Counter = Comments.StoryID) as CommentsCount
FROM MainTable LEFT JOIN Comments ON MainTable.Counter = Comments.StoryID
ORDER BY MainTable.EntryDate DESC

Problem:
Memo fields truncate at 256 characters (255 if you start from 0, whatever) if you use calculations in the select statement. The trick is to embed the calculations in their own query nested within the main query. You really shouldn't have to do that, but hey it works.

 

[dhookom]

I don't see why you need the table Comments in your main query. What does this look like?
SELECT top 25 Counter, EntryDate, URL, TagLine, Description, Category, ImageLink, ImagePercentage, UserName, Aproved, (SELECT Count(StoryID) FROM Comments WHERE MainTable.Counter = Comments.StoryID) as CommentsCount
FROM MainTable
ORDER BY MainTable.EntryDate DESC

Duane
MS Access MVP
Find out how to get great answers faq219-2884.