Text Box Act Like Excel Cell?

[Kevin Sprinkel]

We would like Access to calculate a value based on a
formula like an Excel cell. I thought we might put
overlapping controls on a form, one which stored the
string representing the formula, like:

"+2*6^3+(40+23)*20"

and a second which displayed the result of the
calculation, using the Eval() function. I'd plan to makeg
them appropriately visible and enabled using the keystroke
event.

Is anyone aware of an implementation of this type of
functionality?

Thank you for any assistance.

Kevin Sprinkel
Becker & Frondorf

 

[Rick Brandt]

We would like Access to calculate a value based on a
formula like an Excel cell. I thought we might put
overlapping controls on a form, one which stored the
string representing the formula, like:

"+2*6^3+(40+23)*20"

and a second which displayed the result of the
calculation, using the Eval() function. I'd plan to makeg
them appropriately visible and enabled using the keystroke
event.

Is anyone aware of an implementation of this type of
functionality?

You're over-thinking. Just enter the expression directly into the
ControlSource property of the TextBox and it will display the result.\

EX: a control source of...

=(2+3)*2-1

....will display 9 when the form is opened. More typically, at least one of
the operands would be a field in the form's underlying RecordSet so that a
calculation would be performed per-record.

 

[Kevin Sprinkel]

Thanks, Rick, for your response.

I don't think, however, that this strategy is viable
because each record will have a new formula, and a user
will frequently change the formula as he works. For
example, one of our estimator's tasks is measuring the
lengths of structural steel of various sizes. Typical
data might look like:

LB/LF Length (FT) LB (Calculated textbox control)
(Not stored in table)
65 100 6500
17 85 1445

Frequently lengths come in multiples. For example, if the
estimator counted 25 beams of a 20 FT length, and 4 18 FT
lengths, I'd like him to be able to enter:

LB/LF Length (FT) LB
65 +25*20+4*18 37180


He might later find others and wish to add them to the
entered formula.

Does this make sense?

Best regards.
Kevin Sprinkel

You're over-thinking. Just enter the expression directly

into the ControlSource property of the TextBox and it will
display the result.\

 

[Rick Brandt]

Thanks, Rick, for your response.

I don't think, however, that this strategy is viable
because each record will have a new formula, and a user
will frequently change the formula as he works. [snip]

Ok, I didn't pick up on the fact that the user would be entering the
formula. I've never done anything like that but I would think that EVal()
would do this for you.