By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Sum(or Group By) in Query causing weird characters

P: 5
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.ADJUSTMENTS, [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.ADJUSTMENTS, [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.ADJUSTMENTS
HAVING (((Sum([Property Charges Monthly].[Number Of Items]))<>0))
ORDER BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title];
Dec 5 '06 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 6 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 6 '06 #3

P: 5
Thanks for your responses, NeoPa and Mary!
Item Note is a Memo type- can I fix this? Why would Memo type cause a problem?
Dec 6 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 6 '06 #5

P: 5
would the carriage return cause the squares? should I try to retype all the notes?
Dec 6 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 6 '06 #7

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 6 '06 #8

P: 5
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?
Dec 6 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't think you can group on memo fields in aggregate queries (with sum, count, etc.).

Mary
Dec 6 '06 #10

P: 5
I think I fixed it!!! I put in "is Null or Like *" as the criteria for Item Note and they all came back!!
WHOOOO HOOOOOOO!!!!
Doing the happy dance! (okay I was a little stress because we have been using this database for over 4 years and never had this happen!)

Thanks again for all your help and I hope the solution will help someone else too!
Dec 6 '06 #11

NeoPa
Expert Mod 15k+
P: 31,660
What did you have in the criteria before for the memo field?
What you've got is like having no criteria at all.
Dec 6 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.