Formulas stored in tables!

[Guest]

HI! I would like to know how to retrieve values of stored formulas in table?
When I get the formula in one field in a form, it does not show the value but
the formula itself! Anybody?

 

[Guest]

Sound to me like you are comming spreadsheet! An Access table may look like
an Excel spreadsheet, but it is certainly not. You can not put formulas in a
table and have it calculate anything. For the most part, tables are just for
storing data.

If you need to calculate something in the tables, such as the quantity times
the price, you need to do this in a query, form, or report.

 

[Guest]

No, no, no
I was trying to tell something else!
In a table I have a text cell with a function in it like DSum("X","Y")
In a form I have a control that is taking data from that cell, but I cannot
see the result of the formula, but the formula itself! Could you help me?


"Jerry Whittle" a scris:

 

[mantrid]

You misunderstand what Access actually is. A table in Access simply stores
data. If you type in a formula it doesnt distinguish between that and any
other data such as a number or a sentence. If it is output into a text box
or anything else it will simply display what is stored in the table. To do
your calculations as the previous poster said you will need to do so in a
query, or if you want more flexibility in an sql statement or using VBA but
then that is a whole different ball game.

 

[Dirk Goldgar]

No, no, no
I was trying to tell something else!
In a table I have a text cell with a function in it like DSum("X","Y")
In a form I have a control that is taking data from that cell, but I
cannot see the result of the formula, but the formula itself! Could
you help me?

Depending on the formula, it may be possible to use the Eval() function
to get Access to evaluate the formula and display the result. Suppose
that the field (not "cell") in the table is named "Formula". On a form
bound to that table, you could have a text box with this for its
ControlSource:

=Eval([Formula])

That would probably work, for some formulas at least.

 

[Van T. Dinh]

Set the formula (Field value) as the ControlSource of your TextBox by code.

For example, in one of my databases (for PVC pipe manufacturer) where the
formulation consists of the main component PVC resin and additives (filler,
UV resistant additive, color additive, lubricants, etc ...) and the
quantities of the additives are measured "parts per hundred (parts) of PVC
resin" (PHR), e.g. UV additive 1.5 PHR means for 100 kg of resin, 1.5 kg of
UV resistant additive is required.

Another factor is that each factory may have a number of different mixing
bowls with different capacities. Not only that, some factories prefer to
start with, says 350 kg of resin (our notation "350R") end up with the
resultant mix higher than 350 kg due to the additives. Some other factories
prefer to end up with a fixed amount of of resultant mix, e.g. total 350 kg
at the end and need the database to work out how much resin + how much of
each additive to use to end up with 350 kg total (our notation "350T").

Basically, I end up with the following "BatchFactor" choices (requested by
the users):

250R
300R
350T
350R
....
500T
500R

I use a Table "tlkpBatchFactor" to store the formulae to work out the weight
of each component depending on the PHR of the component (resin amount is
obviously 100 PHR). For example:

For 350R: "=[txtPHR]*3.5"
For 350T:
"=Iif([RecordsetClone].[RecordCount]>0,[txtPHR]/Sum([PHR])*350, 0)"

[txtPHR] is bound to the Field "PHR" in the Table tblFormulationDetail
(1-to-M from tblFormulation to tblFormulationDetail).

The main Form is bound to the tblFormulation, the Subform (in CtsFormView)
is bound to the tblFormulationDetail. In the FormHeader of the Subform, I
have an unbound ComboBox with 3 Columns: BatchFactorID, BatchFactor,
BatchFactorComponentFormula (above). When the user select a Factor, I use
the ComboBox_AfterUpdate Event to set the ControlSource of the TextBox for
the ComponetRequiredWeight like:

Me.txtComponentRequiredWeight.ControlSource = Me.cboBatchFactor.Column(2)

(Hope you understand this long long post.)

 

[Guest]

Thank you all for trying to help, but none of the ideas worked for me!
Yes, I know that Access is not Excel, but I was missunderstanded.
The =Eval([Formula]) does not work for me for the simple fact that I use
another code for retrieving the formula itself. I tried to do something like
that:
Formula_to_retrieve = DLast("Param1", "qTable1","[Criteria]")
The result of Formula_to_retrieve will be something like this:
Result_of_Formula_to_Retrieve = DSum("Param2","qTable2") - this formula
I want to see the result for!
Finally what I want would probably look like:
Result = Eval(Formula_to_retrieve) but it does not work!
Any ideas now??? I sure don't have one, just to change the whole coding!

"Van T. Dinh" a scris:

Set the formula (Field value) as the ControlSource of your TextBox by code.

For example, in one of my databases (for PVC pipe manufacturer) where the
formulation consists of the main component PVC resin and additives (filler,
UV resistant additive, color additive, lubricants, etc ...) and the
quantities of the additives are measured "parts per hundred (parts) of PVC
resin" (PHR), e.g. UV additive 1.5 PHR means for 100 kg of resin, 1.5 kg of
UV resistant additive is required.

Another factor is that each factory may have a number of different mixing
bowls with different capacities. Not only that, some factories prefer to
start with, says 350 kg of resin (our notation "350R") end up with the
resultant mix higher than 350 kg due to the additives. Some other factories
prefer to end up with a fixed amount of of resultant mix, e.g. total 350 kg
at the end and need the database to work out how much resin + how much of
each additive to use to end up with 350 kg total (our notation "350T").

Basically, I end up with the following "BatchFactor" choices (requested by
the users):

250R
300R
350T
350R
....
500T
500R

I use a Table "tlkpBatchFactor" to store the formulae to work out the weight
of each component depending on the PHR of the component (resin amount is
obviously 100 PHR). For example:

For 350R: "=[txtPHR]*3.5"
For 350T:
"=Iif([RecordsetClone].[RecordCount]>0,[txtPHR]/Sum([PHR])*350, 0)"

[txtPHR] is bound to the Field "PHR" in the Table tblFormulationDetail
(1-to-M from tblFormulation to tblFormulationDetail).

The main Form is bound to the tblFormulation, the Subform (in CtsFormView)
is bound to the tblFormulationDetail. In the FormHeader of the Subform, I
have an unbound ComboBox with 3 Columns: BatchFactorID, BatchFactor,
BatchFactorComponentFormula (above). When the user select a Factor, I use
the ComboBox_AfterUpdate Event to set the ControlSource of the TextBox for
the ComponetRequiredWeight like:

Me.txtComponentRequiredWeight.ControlSource = Me.cboBatchFactor.Column(2)

(Hope you understand this long long post.)

--
HTH
Van T. Dinh
MVP (Access)

No, no, no
I was trying to tell something else!
In a table I have a text cell with a function in it like DSum("X","Y")
In a form I have a control that is taking data from that cell, but I
cannot
see the result of the formula, but the formula itself! Could you help me?


"Jerry Whittle" a scris:

 

[Van T. Dinh]

Something wrong with your coding.

I confirmed in A2003 that Eval works fine as Dirk advised.

Just do a quick test in a new database:

* Create a Table "Table1" with a Currency Field "MyPrice" with about 5
records and some valid currency values.
* Create a Table "tblFormula" with a Formula Field (Text data type). Enter
1 Record with the formula:

DSum("MyPrice", "Table1")

In the Debug window, type:

MyFormula$ = DLookUp("Formula", "tblFormula")
(retrieve the formula and store in Text variable MyFormula.)

?MyFormula
(this returns the DSum formula)

MyResult = Eval(MyFormula)
(sum my prices and store in var MyResult)

?MyResult
(this returns the total of MyPrice values)

My method is more complex but it is appropriate for Subform in CtsFormView
for what I needed since in Subform in CtsFormView, all instances of the
unbound TextBox will show the same value. Thus, I need to bind the TextBox
and hence use the ControlSource.

 

[Guest]

Thank's!
Sorry I reply so late! The Eval() function worked great for what I was
trying to do!

No, no, no
I was trying to tell something else!
In a table I have a text cell with a function in it like DSum("X","Y")
In a form I have a control that is taking data from that cell, but I
cannot see the result of the formula, but the formula itself! Could
you help me?

Depending on the formula, it may be possible to use the Eval() function
to get Access to evaluate the formula and display the result. Suppose
that the field (not "cell") in the table is named "Formula". On a form
bound to that table, you could have a text box with this for its
ControlSource:

=Eval([Formula])

That would probably work, for some formulas at least.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)