Storing an expression (not the result) and using in a query

[Lotus49]

Hi all

Being very new to Access I've using massive amounts of Google to develop a database using Access2007 (two screens- one on Access and the other open on Google with an "Access 2007" prefix. Developing software has changed a bit since I last did this 20 years ago!

My application requires a query to produce a set of calculations on stored variables. However, the formulas vary, depending on the recordset (RVCueCard). At the moment I'm using Iif to evaluate which formula to use, based on the RVCueCard_ID:


F1_B: IIf([RVCueCard_ID]=1,((100-[Var_A_B])/100)*([Var_B_B]+[Var_C_B]+[Var_D_B]+[Var_F_B]+[Var_G_B])),IIf([RVCueCard_ID]=3,(((100-[Var_A_B])*[Var_B_B]*[Var_C_B])/100),"CueCard not loaded")

As you can see this is going to become a problem long before I hit the 10(?) entry limit for Iif (as I may end up with hundreds of CueCards!).

What would be ideal would be to store the expression when it is designed with the CueCard, and "drop" it into the query when F1_B is evaluated.

Any ideas on how this could be done?

BTW - in case you missed this earlier - I'm new to this and just figured out how to copy some code into VBA. Please keep it simple

Thanks
Graham

[maximus]

what you are trying to achive is possible. But i need to know what are you trying to achieve. It seems that you are calculating some thing if yes:

1) Does it have a definite Formula.
2) How will the calculation change with the dependent variable.

[Lotus49]

Hi Maximus

The form in question has two columns of variables, being "Base" and "Target" values. These are Var_A_B (variable A in the "Base" column), Var_A_T (a different var A in the "Target column), through to variable G_B, G_T.

The CueCards each look at various business performance improvement areas, using different expressions. The formulas (up to 3 different formulas PER CueCard) are calculated under the Base and Target columns. For example, Var_A may be "hours worked",Var_B may be "cost of manpower", Var_C may be "% of improvement". The first formula (F1_B or F1_T) may calculate "Savings due to productivity gain" using Vars A,B,C in a particular combination (such as in the Iif example shown). The second formula may calculate another result. The results of these formulas are then added to a total "Result" field.

1) Does it have a definite Formula.

The formula will vary according to the individual CueCard

2) How will the calculation change with the dependent variable.

Yes, as explained - the variable will be RVCueCard_ID.

And thanks again for your help on that previous post!

[Lotus49]

It looks as though one can do the job by storing the expression in a formula description field in the table, and then processing it with an Eval expression.

My question now regards usage of the Eval. I've set up a button with an event on the form for testing, and coded as follows:

Private Sub Command283_Click()

f1$ = ((100 - vAt) / 100) * vBt * vCt (hard-coded copy here of string in table)
f2 = Eval(f1$) (check if hard-coded version works)
'F3$ = Eval(Express_F1) (eval of table-version of formula string - rem'd because it doesn't work)
Debug.Print "vAt=" & vAt (check if variables exist and associated values)
Debug.Print "vBt=" & vBt
Debug.Print "vCt=" & vCt
Debug.Print "vDt=" & vDt
Debug.Print "F2=" & f2
'Debug.Print "F3=" & F3
Debug.Print "Express_F1" & Express_F1

End Sub

then I get
vAt=8 (vars present and correct)
vBt=100000
vCt=70
vDt=
F2=6440000 (desired result from hard-coded formula)
Express_F1((100-vAt)/100)*vBt*vCt (confirms Express_F1 is same as hard-coded string)

However, if I Eval the string Express_F1 (which would be the stored string) then I get a message that vAt can not be found. However, vAt evaluates fine in the hard-coded string, and it shows values in the Watch window and debug.print.

I think this has got something to do with syntax, but the topic is not well-covered anywhere I've been able to search.

Any advice on this please?

Also, how would one pass this value to a text-box on the form, considering that it is the result of four other fields on the form (any of which may be updated there).

Thanks