Sum(or Group By) in Query causing weird characters

[Petulant]

Hello~
Help Please~
I am new to Access, so am a little behind the learning curve. I have a query that I have been running for a while that has worked fine and now (with no changes) is throwing up weird characters(like little blocks) in the Item Note field.
(Note : don't know if this infomation is important the only other thing I can think of that has changed in the last month is the size of the database which is 456M and links to 3 other databases smaller in size.)
This is in Access 2000, Windows XP

The query groups items together for each property and if they are the same item sums the number of items. The query works fine if I turn off the Group by(of course it lists all items and no sum)
Here is the SQL view (with out sum on):

SELECT [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], [Property Charges Monthly].[Number Of Items], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], [SumOfNumber Of Items]*(IIf([Price for Misc Items]=0,[Property Charges Monthly]![Item Charge],[Property Charges Monthly]![Price for Misc Items])) AS Expr2, ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJU STMENTS, [Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp.[Item Title] = [Property Charges Monthly].[Item Title]
WHERE ((([Property Charges Monthly].[Number Of Items])<>0))
ORDER BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title];

and here is the SQL View with sum turned on:

SELECT [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], Sum([Property Charges Monthly].[Number Of Items]) AS [SumOfNumber Of Items], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], [SumOfNumber Of Items]*(IIf([Price for Misc Items]=0,[Property Charges Monthly]![Item Charge],[Property Charges Monthly]![Price for Misc Items])) AS Expr2, ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJU STMENTS, [Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp.[Item Title] = [Property Charges Monthly].[Item Title]
GROUP BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJU STMENTS
HAVING (((Sum([Property Charges Monthly].[Number Of Items]))<>0))
ORDER BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title];

[NeoPa]

Funny block characters normally indicate that you have some non-printable characters in your data (Nulls; HTs; VTs; etc).
These would, however, show whether grouped or not.
As you're grouping by the [Item Note] field itself, I can see no reason why it would behave differently when grouped. Sorry.

[MMcCarthy]

Funny block characters normally indicate that you have some non-printable characters in your data (Nulls; HTs; VTs; etc).
These would, however, show whether grouped or not.
As you're grouping by the [Item Note] field itself, I can see no reason why it would behave differently when grouped. Sorry.

If it's a memo data type this would be a problem.

Mary

[Petulant]

Thanks for your responses, NeoPa and Mary!
Item Note is a Memo type- can I fix this? Why would Memo type cause a problem?

[MMcCarthy]

Thanks for your responses, NeoPa and Mary!
Item Note is a Memo type- can I fix this? Why would Memo type cause a problem?

Memo types should only be used to store strings of large sizes where it's unavoidable. They are very difficult to query on. Also users are inclined to use carriage ruturns (paragraph characters) in Memo fields and Access doesn't like them. You will need to try to replace them all with vbCrLf which is the line feed in VBA. But this won't stop users doing it again.

The rule is don't use memo fields unless absolutely necessary and only for things like comments and notes.

Mary

[Petulant]

would the carriage return cause the squares? should I try to retype all the notes?

[MMcCarthy]

would the carriage return cause the squares? should I try to retype all the notes?

Whatever is the easiest way to remove the carriage returns.

Mary

[NeoPa]

would the carriage return cause the squares? should I try to retype all the notes?

A Carriage Return == Chr(0x0D) == VbCr
A Line Feed == Chr(0x0A) == VbLf
A Carriage Return / Line Feed sequence == VbCr & VbLf == VbCrLf
Because records are already tabulated generally (displayed along one row) the showing of any of these could cause the record type display to go wrong, so they show a replacement character (block) instead.

[Petulant]

Mary~
Well it was a lot of typing, but I redid all the memos (and made sure all the blank ones were blank) but I'm still having the problem! AHHHHH! Any other suggestions?

[MMcCarthy]

I don't think you can group on memo fields in aggregate queries (with sum, count, etc.).

Mary