Access 2003 database showing #Error message when viewed in Access 2007

[jparker1954]

I have a formula in a report that I use in an Access 2002-2003 database. This formula works perfectly in Access 2003, but it shows an #Error message when I try to view the report in Access 2007. I cannot upgrade the database to a 2007 version, because most users are still in Access 2003. Is there anything in this formula that might jump out as the problem between viewing it OK in 2003 and getting the error message in 2007?

=Sum(Round((([HarvestingLogQuery]![TotalSumOfGRPCS])*[MaxOfWtGRPCS])+(([HarvestingLogQuery]![TotalSumOfUgRPCS])*[MaxOfWtUgRPCS])+(([HarvestingLogQuery]![TotalSumOfRdRPCS])*[MaxOfWtRdRPCS])+(([HarvestingLogQuery]![TotalSumOfTubs])*[MaxOfWtTubs])+(([HarvestingLogQuery]![TotalSumOfSqRPCS])*[MaxOfWtSqRPCS])+(([HarvestingLogQuery]![TotalSumOfZuRPCS])*[MaxOfWtZuRPCS])+(([HarvestingLogQuery]![TotalSumOfCuRPCS])*[MaxOfWtCuRPCS])+(([HarvestingLogQuery]![TotalSumOfBins])*[MaxOfWtBins])+([HarvestingLogQuery]![TotalSumOfLBS]),2))

[orange]

What is the error number?

Have you tried putting the Round(Sum(..blabla..),2)

[jparker1954]

There is no error number. The report just shows #Error where the total should be. Switching the Round and the Sum doesn't change anything. Still says #Error.

[orange]

How about posting a copy of the database --remove anything confidential?

[jparker1954]

Unfortunately, I can't do that. All I can tell you is that the first formula below is in a field that is in the Detail Dection of a Group with a Group Header and Group Footer. It shows the individual totals for several entries. It works perfectly in both Access 2003 and Access 2007

=Round((([HarvestingLogQuery]![TotalSumOfGRPCS])*[MaxOfWtGRPCS])+(([HarvestingLogQuery]![TotalSumOfUgRPCS])*[MaxOfWtUgRPCS])+(([HarvestingLogQuery]![TotalSumOfRdRPCS])*[MaxOfWtRdRPCS])+(([HarvestingLogQuery]![TotalSumOfTubs])*[MaxOfWtTubs])+(([HarvestingLogQuery]![TotalSumOfSqRPCS])*[MaxOfWtSqRPCS])+(([HarvestingLogQuery]![TotalSumOfZuRPCS])*[MaxOfWtZuRPCS])+(([HarvestingLogQuery]![TotalSumOfCuRPCS])*[MaxOfWtCuRPCS])+(([HarvestingLogQuery]![TotalSumOfBins])*[MaxOfWtBins])+([HarvestingLogQuery]![TotalSumOfLBS]),2)

The formula below is in the Group Footer of the same group and totals the individual results of the formula shown above into one Grand Total. It works perfectly in Access 2003 and generates the #Error message in Access 2007, despite the fact that the only differences are the "Sum(" at the beginning and the extra ")" at the end.

=Sum(Round((([HarvestingLogQuery]![TotalSumOfGRPCS])*[MaxOfWtGRPCS])+(([HarvestingLogQuery]![TotalSumOfUgRPCS])*[MaxOfWtUgRPCS])+(([HarvestingLogQuery]![TotalSumOfRdRPCS])*[MaxOfWtRdRPCS])+(([HarvestingLogQuery]![TotalSumOfTubs])*[MaxOfWtTubs])+(([HarvestingLogQuery]![TotalSumOfSqRPCS])*[MaxOfWtSqRPCS])+(([HarvestingLogQuery]![TotalSumOfZuRPCS])*[MaxOfWtZuRPCS])+(([HarvestingLogQuery]![TotalSumOfCuRPCS])*[MaxOfWtCuRPCS])+(([HarvestingLogQuery]![TotalSumOfBins])*[MaxOfWtBins])+([HarvestingLogQuery]![TotalSumOfLBS]),2))

[jparker1954]

Below is a section of my report that shows the Group I'm having trouble with. The boxed section at the top is the Group Header. The rows of dates and numbers below that is the detail section. The first formula I showed in my previous thread is the Total LBS formula. This formula works perfectly in both Access 2003 and Access 2007.

The Section that shows the Ugly Total: #Error is in the Group Footer. It is where the second formula I listed in my previous thread is located. It Sums up the Total LBS for the individual entries in the group. That formula works perfectly in Access 2003, but it is showing #Error in Access 2007. The other #Errors are because they are linked to that formula.

My question is - is there a way to fix this to show the totals in Access 2007 and still have it work in Access 2003? Or is there an alternative method that will achieve that end? I tried to look into the Totals options for Groups in Access 2007, but for this box, it will only let me use the Count option - the Sum option is not available.

[jparker1954]

I was finally able to get the report to work in BOTH Access 2003 and Access 2007. I ended up testing the formula in 3 separate sections and discovered that the individual sections worked just fine in both 2003 and 2007. So I separated the big formula field into 3 different fields and hid them, then created a new field to add those 3 fields together. Works just fine. The only thing I can figure is that Access 2007 didn't like the number of parameters I had in the original field.