Report asking for parameters and it shouldn't? (1 Viewer)

M Costumes

Member
Local time
Yesterday, 22:08
Joined
Feb 9, 2021
Messages
75
I'm working on a report that is based on a query. In the report footer, I've added some text fields with calculated values--basically creating an invoice and totaling specific fields in the report. The calculations are not part of the query. It was working just fine and doing all the calculations correctly. Now, all of a sudden when I open the form after running the query, it is asking for parameter values for those calculated fields. The formulas in the control source for each text box are correct. Any idea why it would be asking for values it is suppose to be calculating itself? Should the calculations be part of the query?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:08
Joined
May 21, 2018
Messages
8,463
You may have to post what you did. But I find that doing the calculations in the query is simpler and easier to debug then making a calculated control.
 

M Costumes

Member
Local time
Yesterday, 22:08
Joined
Feb 9, 2021
Messages
75
You may have to post what you did. But I find that doing the calculations in the query is simpler and easier to debug then making a calculated control.
Created a new report with control source of a query. In the report footer, I added an unbound textbox, and set the control source to the calculation e.g. =sum[field1]. Repeated for every calculation, so I've got a total of 6 text boxes, but it is asking for parameters for 4 of them.

If I were to put the calculations directly into the query, I would need to write them as Alias: =sum[field1] in their own column in the query design view? And then delete the existing text boxes from the report, and then add them back in as they will then be part of the available fields from the query?
 

M Costumes

Member
Local time
Yesterday, 22:08
Joined
Feb 9, 2021
Messages
75
Ah-ha! So I deleted all my text boxes and started adding them back in one by one with the appropriate formula in the control source to see if I could find the culprit. I had some where I was using one calculated value as part of another formula--as soon as I added one of those back in it started prompting for parameters. I was trying something like this:

txtBox1 = sum([field1]-[field2])
txtBox2 = ([txtBox1]*0.2)

So I'm guessing either this is something you can't do (include a calculated value in a formula for another calculation) or I'm missing something in my syntax.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:08
Joined
May 21, 2018
Messages
8,463
Normally the trick is not to include it
txtBox2 = sum([field1]-[field2]) * .2
 

M Costumes

Member
Local time
Yesterday, 22:08
Joined
Feb 9, 2021
Messages
75
Normally the trick is not to include it
txtBox2 = sum([field1]-[field2]) * .2
Ha! That was what I was secretly afraid of :) I'm going to have some fun formulas, haha! Thanks for your help!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:08
Joined
May 21, 2018
Messages
8,463
If the calculations get too complicated. Sometimes it is easier to do an aggregate query and then do a dlookup or dsum in the footer on the aggregate or other query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:08
Joined
Feb 19, 2002
Messages
42,981
You can refer to a calculated control in the detail section from another control in the detail section but you cannot refer to a calculated control in the detail section from a different section.

WHY? Each instance of the detail section of report uses the same set of properties. Access doesn't store them so you cannot refer to them. However, you can reconstruct them at a different level.

So in the detail section, you have four controls. txtAmt, txtQty, txtExtendedAmt, txtTax. The first two are bound to fields from the RecordSource but the third is a calculation
=Amt * Qty
the fourth is a calculation
= txtExtendedAmt * .075

the fields in the footer are respectively
=Sum(Amt)
=Sum(Qty)
=Sum(Amt * Qty)
=Sum((Amt * Qty) * .075
 

Users who are viewing this thread

Top Bottom