Obtaining expressions stored in table to calculate in query (1 Viewer)

F

fmunoz01

Guest
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

Registered User.
Local time
Today, 07:33
Joined
Feb 26, 2001
Messages
4,018
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.
 
F

fmunoz01

Guest
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

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom