Obtaining expressions stored in table to calculate in query

[fmunoz01]

Does anyone know how to or a simpler way of having a query, in which it's calculated field expressions (formulas) are obtained from a table, instead of the actual query?


Example:

Query
================================
Query Field Name = "SQ FT of Unit"
Query Field Value = Dlookup("[Formula]","Formulas","[FormulaName]='SQ FT of Unit'")


Formula Table
================================
Formula Name = "SQ FT of Unit"
Formula = [Length]*[With]/144


RESULT:
===============================
When you run the query it should calculate to a number value, but instead when you run the query it shows the expression formula([Length]*[With]/144)as text instead.

What am I doing wrong or what can I do to calculate the formula it looks up in another table as an expression, instead of just showing the formula as text when you run the query? Can anyone help?

 

[llkhoutx]

Build a column in your query containing a function whose arguments are all possible fields (columns). Then use a key on a form to indicate the path through the function to perform the requisite calculation.

 

[fmunoz01]

Thanks "llkhoutx" for responding. I'm not sure I understand how to do it through a key in a form. I'd like to just use the "Query" and "Table" concept.

The only reason I would use a form is to edit the expression formula "on the fly".

I just want to be able to have a query field that calls up the expression that is stored in a table and calculate as if the expression formual existed on the query field itself.

Dlookup doesn't seem to be working because it displays it as text, instead of a calculatable expression, when you run the query.

Is there another way of calling up the value of the "query field", which is stored in a table, so that it calculates... or what "wild card" characters am I missing in the "Dlookup Function" that will allow it to calculate as an expression, as opposed to recognizing it as just plain text?

 

[Pat Hartman]

I played around with this for a while and had partial success. You need to use the Eval() function to "execute" the formula. However, I could not get this to work by referencing columns of the query. I only got it to work by referencing form fields or functions. So my formula string looked like:

Eval(Forms!frmData!txtLength * GetWidth() / 144)

Length was a form field and Width was returned by a function.

Let us know how you make out.