Just trying to insert a number in a formula automatically

[jparker1954]

I have the following formula in a Report. When I originally set it up, I did not think that the numbers used as multipliers would change. The 42, 35, 20, 28 listed in this formula refer to weights of containers. Now my boss has decided he need to change the weights on a periodic basis to correspond to seasonal changes.



=Sum(Round((([HarvestingLogQuery]![TotalSumOfGRPCs])*42)+(([HarvestingLogQuery]![TotalSumOfUgRPCs])*42)+(([HarvestingLogQuery]![TotalSumOfRdRPCs])*35)+(([HarvestingLogQuery]![TotalSumOfTubs])*20)+(([HarvestingLogQuery]![TotalSumOfSqRPCs])*28)+(([HarvestingLogQuery]![TotalSumOfZuRPCs])*28)+(([HarvestingLogQuery]![TotalSumOfCuRPCs])*28)+(([HarvestingLogQuery]![TotalSumOfBins])*1000)+([HarvestingLogQuery]![TotalSumOfLBS]),2))

Right now, I'm just going into the report formulas and changing the numbers manually. But there are 6 similar formulas in my report, and changing them manually is tedious and subject to error.

The report is based on the Harvesting Log Query, but I really don't care if the numbers match up or are related to that query or not. They are just multipliers I want to use. I set up a small table that lists the parameters and set up queries that will identify the latest line entered as the one to select a value from. That all works fine. Copy of Query info is below:

ID	Date	GRPCS	UgRPCS	RdRPCS	CuRPCS	SqRPCS	ZuRPCS	Tubs	Bins
4	7/11/2013	42	42	35	28	28	28	20	1000

Instead of the number 42 in the formula below, I like something like [LastRPCWeightsInfoQuery]![GRPCS] to replace the number so that each time the weights are updated, they automatically populate the formula. Unfortunately when I try to enter it into the Report that way, I get a message asking me to enter a Parameter for [LastRPCWeightsInfoQuery]![GRPCS]. How can I get around that? Is there a way to introduce a Query into the formula without have it be directly joined or connected to the HarvestingLogQuery?

What am I doing wrong and how can I fix it? I'm working with almost 7000 records so don't want to go back and redo the original table or queries if I don't have to.

Thanks

[pbaldy]

If the table/query only has 1 record, you can include it in the report's source query without a join and it shouldn't affect the results. That will enable you to refer to those fields in your formulas.

[jparker1954]

That did the trick! Thanks!

If the table/query only has 1 record, you can include it in the report's source query without a join and it shouldn't affect the results. That will enable you to refer to those fields in your formulas.

[pbaldy]

Happy to help!