Report won't calculate

[perrymans]

I have a report and in the detail section, I have an unbound txtTotalCost with a ControlSource of =[txtQTY]*[txtUnitPrice]. In the Group Footer, I have another unbound txtSheetTotal with a ControlSource of =Sum([txtTotalCost]).

I get an "Enter Parameter" message when I go to Print Preview for txtTotalCost. If I hit OK, the report opens with no value in the txtSheetTotal. If I enter a number at the parameter prompt, then I get Error "The Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type" (Error 3169). My data type is set to currency but the same happens no matter what.

My only guess is that it is trying to Sum txtTotalCost before it has been completed. I get #Error if I move txtSheetTotal to the Page Header or Footer.

If I change txtSheetTotal ControlSource to =Count([txtTotalCost]), it still asks me to enter a value, but then it properly counts the number of records, regardless of the number I entered.

I think I remember something about not being able to use Sum sometimes in a report. But I use this exact same setup in other reports and it works fine.

If I remove the equation from txtSheetTotal altogether, the form opens with no prompts.

Thank you for your help. Sean.

 

[cdcampbell]

I am sorry - this is not going to help you but - I am having the same problem if you do figure out how to stop it -& if you felt like sharing your solutions - please let me know. I would owe you bigtime
Thanks

 

[lewatkin]

Hi,

Is your report based on a query? If so, perform the calculation in the query. Then bring the result forward in the report. Then the =sum([fieldname]) in the footer would work just fine.

Lee

 

[perrymans]

You were right lewatkin. I moved the TotalCost: [QTY]*[UnitPrice] to the query, then my sheet total I changed to =Sum([TotalCost]). Notice it did not work with =Sum([txtTotalCost]) which is the text box name. I see it must be the field, not the box.

My only remaining problem now is how do I do a GrandTotal? If only one sheet, then it should equal that sheets total. If 5 sheets, I want to sum all of the Sheet Totals. But I run into the same problem of Summing a Sum text box. When I try to SheetToatl: Sum([TotalCost]) in the query, I get an expression error about not using the first field of the query in the expression.

Thanks again guys. Sean.

 

[lewatkin]

Hi Sean,

I guess I am a "query-holic", but I would do that in a query as well - ensuring that I used the parameters. I would build a query and use the Totals button, Group By the specific criteria, then sum the field that needed to be calculated. Next, I would drop this query in a report and name it "rsubREPORTNAME". I would open the master report in Design view, then drag and drop the rsub from the database window into the master report footer. Open the properties for the rsub and link the master and child fields appropriately, then your report would display the data and the footer (the rsub) would display the total information. The trick here to ensure that the totals are accurate is to make sure that all your criteria is the same for both queries.

Hope this helps!!

Lee