The answer to this is probably very simple, but I couldn't find a sufficient answer searching the forum, so here goes:

I am trying to create a grand total in the report footer by filling in the control source with =Sum([MainTotal]). Main Total (which works) is in a group footer and the control source is =[Total]+[DepositAmount]. [Total] is a field on the main form that pulls a total from a subform footer total. When I open the report, I get prompted to fill in a value for MainTotal. Why is this happening??

(Another unrelated problem - some of my MainTotals are adding up to be about 2 cents off. I think its because the value is being carried out more than 2 decimal places somewhere....how would I fix this?)

Thanks in advance for any help!!!
betsyr

1. You cannot use control names in calculataions. Therefore, you need to recalculate within the Sum() function:
=Sum([Total]+[DepositAmount])

2. Take a look at the "When Access Math Doesn't Add Up" article at www.fmsinc.com for an explaination and possible solution.

Thanks for your response, Pat.

I have another question. To get the desired value, I must have a subreport that calculates one value and then adds it to another on the main report. I can't figure out a way to get all the information I need for this report into one query. Therefore, I was wondering if it was possible to pull a value from a subreport in order to calculate the grand total. I have tried this to reference the field in the subreport query:

=Sum(ArtDepositSubreportQuery.PaymentAmount+[Deposit Amount])

and also

=Sum([PaymentAmount]+[Deposit Amount])

But no luck, I am prompted to fill in the value on Report open. Could just be that I don't know enough SQL to design the query appropriately. Is it possible to build a query within a query? If so, I will begin researching that direction.

Thanks also for the link to that article! I know where the problem is originating and hopefully I will be able to fix it.

Thanks,
betsyr

You cannot reference fields from other queries in a controlsource. If you want to include totals from subreports, you can do it by creating a query to calculate those totals at the level of the main report. For example, if you want to include a transaction total on a customer report, you can create a query tha sums the transactions by customer. Then join the totals query to the main query.