Evaluating expression in field

[Guest]

Is it possible to have a field that stores a formula and then evaluates it in
another field? Example: For each item model (one record) a certain dimension
is calculated differently - so say in model 1, dimension = [length]+2*[width]
and in model 2 dimension=[length]+[width]+[height], etc. How do I get Access
to evaluate the different formulas if I enter them as data?
Thanks for any help!

 

[Duane Hookom]

Try the Eval() function. For instance:
Eval("1+2") = 3

 

[Al Camp]

Mary,
You should never save a calculated value. As long as you have saved the
Length, Width, and Height, you can always recalculate the Mod1Dim and the
Mod2Dim "on the fly" in any subsequent Form, Query, or Report.
Place your Length, Width, and Height "bound" fields on the form. This is
where you'll enter the individual values.
Then, place an unbound text control on your form with a ControlSource
of...
= Length + (2 * Width)
and that will always "display" the correct value... but you do not save that
value
= Length + Width + Height
and that will always "display" the correct value... but you do not save that
value

Use these calculated fields whenever you have to "re-derive" the Mod1Dim
and the Mod2Dim.

 

[Guest]

I guess I don't understand - I need to enter the formula as the data, so as
in your example I need to replace the 1 with [length], 2 with [width], etc.
So when I use the eval function it just gives me my original string "[length]
+ [width]"- this formula changes with each record. What am I missing here?

Try the Eval() function. For instance:
Eval("1+2") = 3

--
Duane Hookom
MS Access MVP
--

Is it possible to have a field that stores a formula and then evaluates it
in
another field? Example: For each item model (one record) a certain
dimension
is calculated differently - so say in model 1, dimension =
[length]+2*[width]
and in model 2 dimension=[length]+[width]+[height], etc. How do I get
Access
to evaluate the different formulas if I enter them as data?
Thanks for any help!

 

[Guest]

But doesn't that mean that I have to use a different field for each different
formula? I had hoped to use one field with a formula that varied by the
value of the model # of that record. So in record 1, dimension would =
length+width but in record 2 that same field would be say
length+width+height.

Mary,
You should never save a calculated value. As long as you have saved the
Length, Width, and Height, you can always recalculate the Mod1Dim and the
Mod2Dim "on the fly" in any subsequent Form, Query, or Report.
Place your Length, Width, and Height "bound" fields on the form. This is
where you'll enter the individual values.
Then, place an unbound text control on your form with a ControlSource
of...
= Length + (2 * Width)
and that will always "display" the correct value... but you do not save that
value
= Length + Width + Height
and that will always "display" the correct value... but you do not save that
value

Use these calculated fields whenever you have to "re-derive" the Mod1Dim
and the Mod2Dim.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Is it possible to have a field that stores a formula and then evaluates it
in
another field? Example: For each item model (one record) a certain
dimension
is calculated differently - so say in model 1, dimension =
[length]+2*[width]
and in model 2 dimension=[length]+[width]+[height], etc. How do I get
Access
to evaluate the different formulas if I enter them as data?
Thanks for any help!

 

[Guest]

Okay, I've figured it out with your hints and for anyone else who might have
wondered you need to use the eval function twice - eval(eval(dim)) - thanks
for pointing me in the right direction!

But doesn't that mean that I have to use a different field for each different
formula? I had hoped to use one field with a formula that varied by the
value of the model # of that record. So in record 1, dimension would =
length+width but in record 2 that same field would be say
length+width+height.

Mary,
You should never save a calculated value. As long as you have saved the
Length, Width, and Height, you can always recalculate the Mod1Dim and the
Mod2Dim "on the fly" in any subsequent Form, Query, or Report.
Place your Length, Width, and Height "bound" fields on the form. This is
where you'll enter the individual values.
Then, place an unbound text control on your form with a ControlSource
of...
= Length + (2 * Width)
and that will always "display" the correct value... but you do not save that
value
= Length + Width + Height
and that will always "display" the correct value... but you do not save that
value

Use these calculated fields whenever you have to "re-derive" the Mod1Dim
and the Mod2Dim.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Is it possible to have a field that stores a formula and then evaluates it
in
another field? Example: For each item model (one record) a certain
dimension
is calculated differently - so say in model 1, dimension =
[length]+2*[width]
and in model 2 dimension=[length]+[width]+[height], etc. How do I get
Access
to evaluate the different formulas if I enter them as data?
Thanks for any help!

 

[Al Camp]

Mary,
Give some examples of your ModelNos, and how you determine from that
ModelNo that it requires a Dim1 or Dim2 calculation.
Then, we can tell you how to control the calculation field to always
display the correct calculation accordingly.
Show several examples, and the corresponding calculations you want to
see.

You can not enter a formula into a text control in form view, and have it
then calculate a value. The formula would only enter as text. A
calculation must be set up in the ControlSource of the Text control.
What you want is do-able, but we need more info...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

But doesn't that mean that I have to use a different field for each
different
formula? I had hoped to use one field with a formula that varied by the
value of the model # of that record. So in record 1, dimension would =
length+width but in record 2 that same field would be say
length+width+height.

Mary,
You should never save a calculated value. As long as you have saved
the
Length, Width, and Height, you can always recalculate the Mod1Dim and the
Mod2Dim "on the fly" in any subsequent Form, Query, or Report.
Place your Length, Width, and Height "bound" fields on the form. This
is
where you'll enter the individual values.
Then, place an unbound text control on your form with a ControlSource
of...
= Length + (2 * Width)
and that will always "display" the correct value... but you do not save
that
value
= Length + Width + Height
and that will always "display" the correct value... but you do not save
that
value

Use these calculated fields whenever you have to "re-derive" the
Mod1Dim
and the Mod2Dim.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Is it possible to have a field that stores a formula and then evaluates
it
in
another field? Example: For each item model (one record) a certain
dimension
is calculated differently - so say in model 1, dimension =
[length]+2*[width]
and in model 2 dimension=[length]+[width]+[height], etc. How do I get
Access
to evaluate the different formulas if I enter them as data?
Thanks for any help!