Sum Calculated Text Box in Report Header Receiving Error Access 2010

[aytee111]

An expression is something typed in, a value that does not come directly from a table or a query. It can be a calculation, a lookup, etc.



Post # 9 says that unit price is a calculation - ? You are being lazy by refusing to retype this calculation in your total field!!

[MPXJohn]

Being lazy has nothing to do with it. That was never suggested. Regardless, I tried that and it did not work either. Unit Price is a text box with an expression in it as stated in Post #9. When I put the summation I am trying to get to work in the footer it I noticed that it was asking for an input of the Unit Price. So that told me there was something amiss there. I tried the suggestions of brackets with no success. Do you see anything in that IIf statement that might be causing the problem? My plan is to next IIf statements to include four price breaks.

[isladogs]

As both aytee & I have already said, you can do this in two ways:
1. Using expressions like =Sum([Cost Packet Quantity]*[Unit Pricing]) in the report where each item in the expression is a field value
2. Using expressions in a query and using that as your report record source

What you can't do is use expressions based on values calculated earlier e.g. Sum([Total Price])
You do need to ensure the field totals are displayed in a later section of your report to the fields themselves
You claim to have already tried everything but somewhere along the line you aren't doing this correctly as the method is absolutely standard and it works.
So if you are still stuck I suggest you post a stripped down version of the database with the report, tables/queries used as its record source & anything else needed to make it work

[MPXJohn]

BOM TRIAL 5 - Copy.zipOkay, I understand now. I did not realize you could not use expressions based on values calculated earlier or that it was standard to have the field totals displayed in the later section.
Here is a stripped down version of the Database. I have the calculations working in the query. But this query is not available to the BOM currently. I do not know how to add it to the list. I am guessing in the relationships, but I am not sure how to accomplish that. I added the query to the relationships window, but haven't made a relationship yet. Thanks for the continued help.

[isladogs]

Both of those points were made in previous posts ....

Anyway, I've downloaded it but please tell me
a) which report
b) what to enter in the parameter prompts to save me having to keep looking things up in your tables

Thanks

[MPXJohn]

Sorry about that. Meant to delete the second report.
Report "Cost_Packet_Report"
Enter '2689' when prompted. This is the ID of the customer in the 'Customer_Part_Numbers' table and is the one I was using for testing the calculations, so it has all the data needed in it.
The 'Total Price' prompt is one of the errors happening since I moved the summation to the footer.

[isladogs]

Sorry about that. Meant to delete the second report.
Report "Cost_Packet_Report"
Enter '2689' when prompted. This is the ID of the customer in the 'Customer_Part_Numbers' table and is the one I was using for testing the calculations, so it has all the data needed in it.
The 'Total Price' prompt is one of the errors happening since I moved the summation to the footer.

The reason you get the second prompt for unit_price is because that is itself a calculated value.
So your totals field needs to use the original fields for that part as well

=Sum([Cost Packet Quantity]*(IIf([Order Total]>[Price Break4],[Price Break4 Cost]+([Shipping Charges]/[Order Total]),[Cost])))

This now works 'perfectly' and the total is shown

If you prefer, the textbox could be unbound & code used in the Report_Footer On Format event to set that:

MytextBox=Sum([Cost Packet Quantity]*(IIf([Order Total]>[Price Break4],[Price Break4 Cost]+([Shipping Charges]/[Order Total]),[Cost])))

BTW Your report is far too wide to print on one page

[MPXJohn]

Perfect! I knew it was too wide, will deal with that later. Thanks for all your help and patience with me.