Using dynamic control source expressions on unbound fields

[LuisTeknova]

Hello,

I will do my best to describe what I have been trying to acheive. I have a report, that currently uses a control source expression to determine a value. Field name "MfgScale" control source "=([StartQty]*([FillVolume]/1000))" I would like to query the control source for MfgScale from a field called "Formula" in another table called OverageCalculations which is a text field with the value "=([StartQty]*([FillVolume]/1000))". The Table OverageCalculations has a "PartNumber" Field which is the unique identifier which means "PartNumber" can have a different formula and the formula can be the control source expression for MfGScale. The report uses a field called ItemID which can be joined with PartNumber. Is this possible? Hope this made sense.

[jzwp11]

If I understand you correctly, the only way to do what you describe is with VBA code, but even with that I do not think it will work if you are bringing multiple items into the report. The control source of a text box applies to all records within the section in which it is located.

You said that each part number can have a different formula. How do those formulas differ? Do the formulas differ based on the type of part. So do parts of one type use 1 formula while parts of a different type use a different formula?

Could you provide some examples? Perhaps there is another way to handle this.

[LuisTeknova]

Yes I think you understand correctly. example below
Table - OverageCalcualtions
PartNumber | Formula
Part1 | =([StartQty]*([FillVolume]/1000))
Part2 | =([StartQty]*([FillVolume]/1000)+1.5)

Report - BatchRecord
ItemID = PartNumber
MfgScale,unbound textbox, control source=OverageCalculation.Formula(expression) where ItemID = PartNumber

I tried to make this logic work but have only had success making MfgScale's output be =([StartQty]*([FillVolume]/1000)) not control souce expression. I do this as an OnOpen Event of the report.

[June7]

Include OverageCalculations table in report RecordSource by joining pk/fk fields then the related info is available.

If the only variation in the formula is the overage addition, just include that value in a field then that field can be referenced when the tables are joined.

=[StartQty]*([FillVolume]/1000)+Nz([OverageFactor],0)

Is the OverageFactor supposed to be added before multiplication by StartQty?

[LuisTeknova]

By including the table couldnt I then, reference the formula for the control souce for MfgScale while producing the result rather than the formula output? I like the solution above but Im still trying to figure out if this is possible.

I never thought of using the [OverageFactor] as a varible, but it may work. BUT, not to complicate things.. I used my easiest formula.. going that route I may need two or more [OverageFactor]S per product. example.
=IF([WKO_StartQty]<3,(([WKO_StartQty]*([FillVolume]/1000))+0.75),IF([WKO_StartQty]<10,(([WKO_StartQty]*([FillVolume]/1000))+1),(([WKO_StartQty]*([FillVolume]/1000))+2)))
as seen above [OverageFactor] is added after multiplication.
This may work. but I will just have to see if The overage is the only variation.

[jzwp11]

I do not think that Access will recognize it as a formula in a query since the value is still in a text field. BTW, if you want to do comparisons in a query you want to use the IIF() function not IF().

[June7]

I see, that is 3 different overage factors and would require 3 fields in table.

If you want to store formula as a string in a field then think will need the Eval function, but I have never tried this.

=Eval([formula field name])

[LuisTeknova]

Yes Thats correct 3 overage factors.

No Luck with =Eval([formula]) did that and didnt work, but input the value of [formula] into the =Eval() and that worked so I know the formula equation worked, but not the method of using the field name.

[June7]

Then only suggestion is 3 overage factor fields and an IIF() expression, assuming the overage factor is the only variance in the formula:

=[WKO_StartQty]*[FillVolume]/1000+IIF([WKO_StartQty]<3, 0.75,IIF([WKO_StartQty]<10,1,2))