Calculated Field Not Displaying Correctly on Subreport (1 Viewer)

hascet · Sep 14, 2014

I have been trying to figure out why my subreport is only showing sales price for only one of the records on my subform. Everything else works as it should, but it only displays the sales price for the record which is active on the subform.

Attached is the database with the subreport called rptProposalItems with the field 'Sales Price' which is experiencing the issue. There is sample data so you can see the issue for yourself.

JHB · Sep 14, 2014

Because the control source is "=[Forms]![Navigation Form]![NavigationSubform].[Form]![frmProposalItems].[Form]![Sales Price]", then only the first record is shown, you need to do the calculation in the query the report is based on.

hascet · Sep 14, 2014

JHB said:
Because the control source is "=[Forms]![Navigation Form]![NavigationSubform].[Form]![frmProposalItems].[Form]![Sales Price]", then only the first record is shown, you need to do the calculation in the query the report is based on.

JHB,

Thanks fro the quick reply. I have tried that, attached below is my attempt at including the calculation in my report source query. I am getting the same result as when the control source is set to reference the subform field.

JHB · Sep 15, 2014

You are still referring to the form in the query, it was not what I meant!
You need to do the same calculation in the query, as you do to get the value of the "Sales Price" in the form.
Only for info: You can refer to a main form from a sub-form by using the word Parent.

hascet · Sep 16, 2014

JHB said:
You are still referring to the form in the query, it was not what I meant!
You need to do the same calculation in the query, as you do to get the value of the "Sales Price" in the form.
Only for info: You can refer to a main form from a sub-form by using the word Parent.

I think I'm understanding you, in the record source query I created a new expression called 'Price' which calculates the same value as the 'Sales Price' field. I placed the 'Price' expression on my report and it calculates the value I want, but still the value for 'Price' is the same for every record when it should be different dollar amounts.

JHB · Sep 16, 2014

hascet said:
... and it calculates the value I want, but still the value for 'Price' is the same for every record when it should be different dollar amounts.

Where is it the same amount, in the query or in the report?
Post you database with some sample data, + info how to get the result you get, zip it.

hascet · Sep 16, 2014

JHB said:
Where is it the same amount, in the query or in the report?
Post you database with some sample data, + info how to get the result you get, zip it.

JHB,

The report in question is called rptProposalItems and the record source is qryProposalReportItems. If you look at the database, you'll see the sample data. There are two different records and they should have different sales prices, but for some reason both records show the same sales price ($1.02). Both the report and query are experiencing this issue, which leads me to deduce that it is a query issue most likely, but where the issue is I am unsure.

Attached is the zipped database file. Thanks for your troubleshooting help so far.

JHB · Sep 17, 2014

Sorry to say it but you are still referring to the subform in your query, you must do the calculation for [ExtCost] in the query also.
Take in mind if you refer to a subform only the first record is returned.

Code:

 (([Forms]![Navigation Form]![NavigationSubform].[Form]![B][COLOR=Red][frmProposalItems][/COLOR][/B]![ExtCost]
  +[Forms]![Navigation Form]![NavigationSubform].[Form]![Total])
  *[Forms]![Navigation Form]![NavigationSubform].[Form]![Contingency])
  +(([Forms]![Navigation Form]![NavigationSubform].[Form]![B][COLOR=Red][frmProposalItems][/COLOR][/B]![ExtCost]
  +[Forms]![Navigation Form]![NavigationSubform].[Form]![Total]
  +((([Forms]![Navigation Form]![NavigationSubform].[Form]![B][COLOR=Red][frmProposalItems][/COLOR][/B]![ExtCost]
  +[Forms]![Navigation Form]![NavigationSubform].[Form]![Total])
  *[Forms]![Navigation Form]![NavigationSubform].[Form]![Contingency])))
  *[Forms]![Navigation Form]![NavigationSubform].[Form]![Markup])
  +[Forms]![Navigation Form]![NavigationSubform].[Form]![B][COLOR=Red][frmProposalItems][/COLOR][/B]![ExtCost]
  +[Forms]![Navigation Form]![NavigationSubform].[Form]![Total]

Calculated Field Not Displaying Correctly on Subreport (1 Viewer)

hascet

New member

JHB

Have been here a while

hascet

New member

JHB

Have been here a while

hascet

New member

JHB

Have been here a while

hascet

New member

JHB

Have been here a while

Similar threads

Users who are viewing this thread