Access Total Query bringing up weird characters?

  • Thread starter Thread starter Petulant
  • Start date Start date
P

Petulant

Hello-
I am experiencing a weird problem. The database has been functional for
4 years. Now one of the total queries is spewing garbage characters????
The database is only 450M and links to two other databases about the
same size. If I take off the sum (or group by) feature on the query it
lists everything fine (except no totals). I am using Access 2000.
Any suggestions??
 
Please provide the SQL and an example of the weird characters. Please note
which field(s) have the weird characters and their datatypes.
 
If anyone could please help???? I'm really new to Access and can't see
where there was a change in the program from last month - other than
the database size is larger.

Here is some additional information- the query works fine until I click
on the Totals option to request a sum of one of the fields (want to get
sums of groups)

This one is with out the sum: (works here)
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]
ORDER BY [Property Charges Monthly].[Property Number], [Property
Charges Monthly].[Item Title];

And here it is with the sum:(gives weird characters in the Item.Note
and incorrect sum of Number of Items)

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]
GROUP BY [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], ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge],
ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge],
ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJUSTMENTS, [Statement
Month]
ORDER BY [Property Charges Monthly].[Property Number], [Property
Charges Monthly].[Item Title];

Thanks for your time!
 
What data type is [Property Charges Monthly].[Item Note]? If it's a memo,
unlikely, or a text field filled with miscellaneous notes, try removing it
from the query and see what happens.

It's possible that you have a corrupt database. Try a compact and repair.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Petulant said:
If anyone could please help???? I'm really new to Access and can't see
where there was a change in the program from last month - other than
the database size is larger.

Here is some additional information- the query works fine until I click
on the Totals option to request a sum of one of the fields (want to get
sums of groups)

This one is with out the sum: (works here)
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]
ORDER BY [Property Charges Monthly].[Property Number], [Property
Charges Monthly].[Item Title];

And here it is with the sum:(gives weird characters in the Item.Note
and incorrect sum of Number of Items)

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]
GROUP BY [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], ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge],
ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge],
ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJUSTMENTS, [Statement
Month]
ORDER BY [Property Charges Monthly].[Property Number], [Property
Charges Monthly].[Item Title];

Thanks for your time!
Click to expand...
 
Jerry~
Thank you for the suggestion. I did remove the item note and the
correct sums came up. I did the compact and repair on each database.
However, I do need the item note (explains why they are being charged),
but when I add it back in, I still get the strange boxes even after
the compact and repair. Oh, yes, I looked at the Property Charges table
and Item Note is a memo. Is that a problem?
If Item Note is corrupt - is it possible to go back through and retype
all of them to repair? Or does compact and repair really repair?
 
Wait guess what? At first I thought someone put in a <return> (carriage
return) so retyped all items....but still didn't work! Then I typed in
"Is Null or Like "*" " and Wham!!! All the Item Notes came back and the
sum function is working.
Thanks so much for your help and I hope this solution helps everyone
else!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to display highest totals 1
Access Query problem 1
Pipe character in query in VB6 app 11
Totals query and Memo field 3
Weird characters in query results 1
Query Sum return incorrect total. 4
access 2003/2007 - problems with unwanted pipe character 0
Really weird query responses. 2

Back
Top