More on “Data type mismatch in criteria expression.”

[hilian]

I am summing the results of a calculated field from a query in a report. The report is grouped at two levels, and I need to sum at two levels. The amounts I am summing are expenditures by social service agencies. Each agency has several programs. I need a sum for each program and then for each agency. I am able to get a sum for program by putting the field (called Expr10) in the Program group footer. However, when I try to sum the sums by putting the field in the Agency group footer, I get the error message, “Data type mismatch in criteria expression.” I’ve tried it several ways: by simply putting the field in the Agency footer; by putting it in the footer and changing the Running Sum property to “over group;” and by using the formula =sum([Expr10]). I can also put the field in the Agency group footer without putting it in the Program group footer, and it will work. But I can’t put it in both places. I’ve tried other fields in the report, and I can get sums at both levels, just not Expr10. Can anybody help?

Thanks,

Henry

 

[Beetle]

Are you able to post a copy of your db with any sensitive data removed? Seems like an unusual problem so it might help you get a solution if we have an opportunity to see what's going on in your app.

 

[hilian]

How do I do that?

 

[hilian]

I saw how to attach the application, but I would have to sanitize a huge amount of confidential information. I can do it, but I would be duplicating the application rather than attaching the original one. I'll try to do that.

 

[Beetle]

I would suggest importing all of your objects into a new database, then testing for that same problem in the new database (just in case it's some quirky corruption issue). If you still have the same problem in the new db, then just delete the existing data and re-enter just enough bogus data to be able to re-create the problem and upload a copy. If this is going to be too large of an undertaking then we can try to resolve the issue without you uploading a copy.

 

[hilian]

Hi Beetle,

I managed to sanitize the data reasonably quickly. I've uploaded the file. Hope you can find what went wrong.

Many thanks,

Henry

 

[hilian]

Another expert stepped in and found the solution to the problem. It was that when I imported the main data table from Excel, fields that should have been numeric came in as text. It never occurred to me to look. I changed the fields to numeric, and I was able to sum on both footers. Whenever I import tables in the future, I will know to confirm that all fields are designated correctly. Sean, thank you for your help. Following your suggestion, I actually recreated the data base but got the same result. I was able to rule out corruption as the problem.