String Handled as a Formula

  • Thread starter Thread starter TimWillDoIt
  • Start date Start date
T

TimWillDoIt

I need to assign a formula to a string variable and then use the string
variable in my code as though it were the formula itself.

I've found reference to the .Formula Method for some applications, which
seems to be what i want to do. But this Method doesn't seem to apply in
Access.

A simplified example of what I need to do would be:

Dim strFormula As String
Dim intResult As Integer

strFormula = "5 * 5"
intResults = <some function>(strFormula)
MsgBox intResults

....and the Message Box would display 25.

Can this be accomplished?
 
Hi -

Try the eval function. For example

Eval(5*5 + 10) wil yield 35 as expected.

It will also handle functions: eval(sqr(9)*10) --> 30.

John


I need to assign a formula to a string variable and then use the string
variable in my code as though it were the formula itself.

I've found reference to the .Formula Method for some applications, which
seems to be what i want to do. But this Method doesn't seem to apply in
Access.

A simplified example of what I need to do would be:

Dim strFormula As String
Dim intResult As Integer

strFormula = "5 * 5"
intResults = <some function>(strFormula)
MsgBox intResults

...and the Message Box would display 25.

Can this be accomplished?
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 
From what I can see, using Eval() in my test code, it looks like this is
going to give me exactly what I need. Thanks.
 
My initial testing, using:

strFormula = "5 * 5 + 10"
MsgBox Eval(strFormula)

worked perfectly. As I'm adding some complexity, though, I'm running into
trouble. Part of the formula needs to be an integer variable. For example:

Dim strFormula As String
Dim intHrs As Integer

intHrs = 10
strFormula = "intHrs * 1.5"

MsgBox Eval(strFormula)


This, of course, gives me the message, "Microsoft Office Access can't find
the name 'intHrs' you entered in the expression."

I've tried variations of how I assigned strFormula, but I can't get it to
work. The following works:

strFormula = intHrs & " * 1.5"

but I'm going to pull the value of strFormula from a table field so it all
needs to be enclosed within the quotes. Any suggestions??
 
I've tried variations of how I assigned strFormula, but I can't get it to
work. The following works:

strFormula = intHrs & " * 1.5"

but I'm going to pull the value of strFormula from a table field so it all
needs to be enclosed within the quotes. Any suggestions??
Click to expand...

What would be actually in the table field?

You could try

strFormula = Replace(strFormula, "intHrs", intHrs)

to take a string from the table such as

"intHrs * 1.5"

and convert it to

"5 * 1.5"

if that's the value of the variable.

I really have to wonder whether this is a good way to get your job done
though, it's going to be REALLY inefficient!
 
John,

Thanks. the Replace() function did exactly what I needed it to. You're
right...and I see the inherent inefficiency of pulling my formula from a
table field. I'll explain why I'm doing it this way and maybe you can
suggest a better alternative.

I've been given a task to create a job costing module. When you select a
job type (i.e.; Recessed Lighting Install, Pool Pump Install, etc.), there
will be unique conditions depending of the job type selected.

For example, if 'Recessed Lighting Install' is selected, one of the
unique conditions will be, "Is there attic access?" If chkAtticAccess =
True, then the number of hours needed to complete the job is multiplied by
1.5.

Simple enough to hard code this. But after I'm finished with the
programming, they'll need to come back and add job types...some with unique
conditions. I can't come back and modify the code each time they add or
remove a job type from the list, so I need to be able to add the formula to
the list of job types.

This way, if 'Recessed Lighting Install" increases the number of hours to
complete the job by 50% or a 'Sub-Panel Install' increases the layout cost by
100%, the program can just pull the formula from the list of job types and
apply it to the condition.

I would love for there to be a better way to acheive this other than
having to come back and modify the code each time there's a change of unique
conditions. Any ideas? Thanks.

Tim
 
Back
Top