Problem with GROUPBY and memos

[ColinSully]

Hi, I am new to working with Access and am working with a database that has already been created.

One fo the modifications I need to make is to change the data type of a comments field from text to memo.

I have succeeded in doing this, however, I get an error when I try to run one of my queries. It appears that Access "can't group on memo" I tried modifying the GROUPBY attribute in SQL but I'm not entirely sure what I'm doing.

The SQL currently reads:

SELECT MAX(MaxComments.DateReference) AS MaxDate, SubNotNullComments.DateReference, SubNotNullComments.Comments, MaxComments.ProjectID

FROM SubNotNullComments INNER JOIN SubNotNullComments AS MaxComments ON MaxComments.ProjectID = SubNotNullComments.projectID

GROUP BY MaxComments.ProjectID, SubNotNullComments.DateReference, SubNotNullComments.Comments

HAVING ((Max(MaxComments.DateReference))=[SubNotNullComments]![DateReference]);

I have bolded the 2 occurences of the comments field that I changed to memo if that helps.

Any ideas on how I might be able to solve this problem would be greatly appreciated.

Many Thanks,
Colin

 

[Norie]

Do you really need to change the data type?

If the data you want to store there is >255 characters you might want to consider a seperate table for comments where each Project can have multiple comments.

 

[ColinSully]

I do need to have >255 characters for each comment.

As for the seperate table, I don't know how that would solve my problem as I still need to get the comments onto the querie.

 

[bat17]

I dont think that you can group by Memos, I seem to recal a problem as well with sorting on memo fields which can truncate the data back to 255 characters when you try.
The problem is to do with the fact that Memos can hold litraly thousands of chracters and you would have to compare each one in each record, lots of work!

Peter

 

[Norie]

I do need to have >255 characters for each comment.

Silly question but why?

 

[ColinSully]

Because at times people may want to enter comments in that are bigger than 255 characters.

I don't even want this thing sorted or grouped by the memo field. I am just looking for a way to remove it from the grouping but still be able to see the memo when I print the query/report.

 

[bat17]

I am not quite sure what you are doing here, if you use grouping in a query the each field shown either has to be used to group by, or some sort of calculation needs to be preformed upon it.

Are you sure that you need to use grouping at all? if you want each memo field to be seen, then each record must be available, so no grouping is needed in the query.

You can use "Sorting and Grouping" in the report itself to control how things will be oredered and displayed.

Peter

 

[ColinSully]

No I didn't need to use grouping. I think I figured it out.

As I said I am new to Access and am working with a database that was already set up. The records in question didn't need to be grouped.

Thanks for the help