Converting a String to Calculation to Produce a Sum

  • Thread starter Thread starter Dominic Smith via AccessMonster.com
  • Start date Start date
D

Dominic Smith via AccessMonster.com

As a way of future-proofing an Access Database, I have built a table where
the user can enter calculations as text e.g.
Tax Rate * Discount Rate /(Capital Spend - 1) + (Warranty - 5)

Each item will have a corresponding amount somewhere in the database. The
base calculations will always be the same but the numbers will vary
depending on the criteria a user chooses.

I've got code that will build the calculation and produce the following
string -

9 * 10 / ( 88 - 1 ) + ( 27 - 5 )
(there are no spaces by the way, I've put them in to make it clearer)

What I can't do is make VB perform the actual calculation to return the sum
- I've tried converting it at various points but I still get an type
conversion error and/or just the string.

Assigning a variable to the calculation in the debug window works but I
can't seem to convert the calculation string into an actual sum within the
code.

Is there a straightforward way of simply converting a string to a
calculation in the same was as Cdbl or Cstr works?
Or does anyone have any clues as to the best way of doing this.

Thanks in advance
Dom
 
Dominic said:
As a way of future-proofing an Access Database, I have built a table where
the user can enter calculations as text e.g.
Tax Rate * Discount Rate /(Capital Spend - 1) + (Warranty - 5)

Each item will have a corresponding amount somewhere in the database. The
base calculations will always be the same but the numbers will vary
depending on the criteria a user chooses.

I've got code that will build the calculation and produce the following
string -

9 * 10 / ( 88 - 1 ) + ( 27 - 5 )
(there are no spaces by the way, I've put them in to make it clearer)

What I can't do is make VB perform the actual calculation to return the sum
- I've tried converting it at various points but I still get an type
conversion error and/or just the string.

Assigning a variable to the calculation in the debug window works but I
can't seem to convert the calculation string into an actual sum within the
code.
Click to expand...


Use the Eval function.
 
As a way of future-proofing an Access Database, I have built a table where
the user can enter calculations as text e.g.
Tax Rate * Discount Rate /(Capital Spend - 1) + (Warranty - 5)
Click to expand...

This is an invalid calculation. Space characters ARE MEANINGFUL - you,
as a person, see Tax Rate as one thing. Access (or any computer
program) sees it as two - something named "Tax" and something else
named "Rate".
Each item will have a corresponding amount somewhere in the database. The
base calculations will always be the same but the numbers will vary
depending on the criteria a user chooses.

I've got code that will build the calculation and produce the following
string -

9 * 10 / ( 88 - 1 ) + ( 27 - 5 )
(there are no spaces by the way, I've put them in to make it clearer)
Click to expand...

.... ok... good. Parsing the text must have been a real chore!
What I can't do is make VB perform the actual calculation to return the sum
- I've tried converting it at various points but I still get an type
conversion error and/or just the string.
Click to expand...

Try the Eval() function:

?Eval("9 * 10 / ( 88 - 1 ) + ( 27 - 5 )")
23.0344827586207


John W. Vinson[MVP]
 
Back
Top