Correct Parenthesis

[0bsidian]

I currently have a parent report, with an embedded subreport.

In the report footer of the parent, I have a selection of text boxes which do a series of calculations to give me report totals.

I want one of them to take a figure from the parent (Total PurchasesIncVAT) and deduct a figure from the subreport(TotalBuyBackFigure) however, the formula I have used seems to be having difficulties as I get a #Name? error.

I am using the following:

=([TotalPurchasesIncVAT])-Reports!srptAllBuyBackTrans!TotalBuyBackValue

Is my formula wrong, and if so, can you suggest what would be the correct formula?

Many thanks,

0bs.

 

[boblarson]

If the subreport is on the same report as the text box you are trying to set the value for, it would be:

=([TotalPurchasesIncVAT])-[srptAllBuyBackTrans]![Report].[TotalBuyBackValue]

And the control that is on the main report that houses the subreport needs to be the name used if srptAllBuyBackTrans is not the name of that control. Also, notice the period and not bang, before [TotalBuyBackValue]. The [Report]. part remains as written, do not substitute anything for that word.

 

[0bsidian]

Thanks Bob.

I have changed my formula to the one you suggested, and I have checked (I think) the name of the control is the same as contained within the formula - unfortunately, I still get the same error.

I've included a screenshot in this post to let you check if I have misinterpreted what you have said. I'd be most appreciative if you could take a look for me and see if thats the case. (it's the Total Takings field I am putting the formula into and I have highlighted the subreport to show the control properties on the right).

Again, my thanks.

0bs.

 

[boblarson]

You're missing the square brackets that I included.

 

[0bsidian]

I thought the same, but Access seems to take them away once I've entered the exact formula you gave me. I even copy-pasted your formatting in, but once you change from design mode to view mode (and see the #Name error), it seems to remove the brackets.

I'm not sure why that is. Do you think there is a setting that I might have incorrectly configured that is causing that?

0bs

 

[boblarson]

Not sure why it would do that. Also, you can't reference the TotalPurchaseIncVAT that way. In your current formula you will have to use =Sum([ValueIncV]) - the other part as well.

 

[0bsidian]

Hmm..not sure about the bracket thing - will look into that.

I can't use the result of another formula (in this case the formula that totals BuyBack) in the formula on the parent report? Darn, I wasn't aware of this.

Apologies for being a bit thick, but I am not sure I follow your explanation of how I would get round this. Would you be willing to elaborate a little please?

0bs

 

[boblarson]

It would be something like:

=Sum([ValueIncV]) -[srptAllBuyBackTrans]![Report].[TotalBuyBackValue]

But if you really want to get the TotalBuyBackValue from the subreport, you can use this method (even though it is for forms, it works for reports too, if you go to the report), using the Builder, to ensure you get the exact syntax.

 

[0bsidian]

Excellent stuff Bob.

I opted for the latter approach, which I thank you for directing me to. Good work.

0bs