Access Total Query bringing up weird characters?

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??
 
G

Guest

Please provide the SQL and an example of the weird characters. Please note
which field(s) have the weird characters and their datatypes.
 
P

Petulant

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!
 
G

Guest

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...
 
P

Petulant

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?
 
P

Petulant

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 Sum return incorrect total. 4
Weird characters in query results 1
Sum total in query produce weird decimals 1
Access Query problem 1
Customer Query 1
"is not a valid name. Make sure 7
Counting multiple field values in a single query/report 1
I need to sum up some values in a column of a query 2

Top