Creating Table driven Calculations

G

Guest

I would like to have the calculations I am performing on fields in a Pricing
table come from a calculations table (thus allowing me to extend the amount
of calculations I do on a pricing file by simply adding additional records in
the calculations table and not having to hard code them all in VB). In the
calculation table, I have a field called Calculations, when I pull the field
out via ADO, I am unable to get the result since I can’t seem to evaluate the
formula from the Calculations field since VB only see it as a string. For
example;

Table Schema’s:
Table Price
PriceField1 = 5.50
PriceField2 = 2.50
PriceField3 = 1.00

Table Calculation
CField1 = rcdPriceField1 – rcdPriceField2
CField2 = Max(rcdPriceField1, rcdPriceField2, rcdPriceField3)

VB Code (not working yet)

Dim curResult1 As Currency, curResult2 As Currency, curResult3 As Currency
‘ setup info and record opens
‘ADO objects
rcdPPrice.Open strSQL, cn, adOpenStatic, adLockReadOnly
rcdCalculation.Open "SELECT * FROM tblCalculation, cn, adOpenStatic,
adLockReadOnly

Do Until .EOF
‘ I wish to be able to have the cal pulled from the table then processed,
such as
curResult1 = rcdCalculation!CField1
curResult2 = rcdCalculation!CField2
curResult3 = rcdCalculation!CField3

rcdPPrice.MoveNext

‘ extra processing logic here
Loop

‘ clean up and exit.

' * * * * end code * * * * *

Any ideas on how I can load my calculations in a table, then pull them out
and have them executed in VB?

Thanks in advance for your assitance,
Mike P.s
 
M

Marshall Barton

Mike said:
I would like to have the calculations I am performing on fields in a Pricing
table come from a calculations table (thus allowing me to extend the amount
of calculations I do on a pricing file by simply adding additional records in
the calculations table and not having to hard code them all in VB). In the
calculation table, I have a field called Calculations, when I pull the field
out via ADO, I am unable to get the result since I can’t seem to evaluate the
formula from the Calculations field since VB only see it as a string. For
example;

Table Schema’s:
Table Price
PriceField1 = 5.50
PriceField2 = 2.50
PriceField3 = 1.00

Table Calculation
CField1 = rcdPriceField1 – rcdPriceField2
CField2 = Max(rcdPriceField1, rcdPriceField2, rcdPriceField3)

VB Code (not working yet)

Dim curResult1 As Currency, curResult2 As Currency, curResult3 As Currency
‘ setup info and record opens
‘ADO objects
rcdPPrice.Open strSQL, cn, adOpenStatic, adLockReadOnly
rcdCalculation.Open "SELECT * FROM tblCalculation, cn, adOpenStatic,
adLockReadOnly

Do Until .EOF
‘ I wish to be able to have the cal pulled from the table then processed,
such as
curResult1 = rcdCalculation!CField1
curResult2 = rcdCalculation!CField2
curResult3 = rcdCalculation!CField3

rcdPPrice.MoveNext

‘ extra processing logic here
Loop

‘ clean up and exit.
Click to expand...


This is a pretty tricky situation. The first thing is to
look in VBA Help for the Eval function.

The next thing is that the Expression Service (Eval uses it
to evaluate the expression in your string) is not aware of
VBA variables. This means that you need to use the Replace
function to substitute the variable's value for the variable
name.

Using your first example,
x = rcdPPrice!PriceField1
y = rcdPPrice!PriceField2
result = Eval(Replace( _
Replace(curResult1, "rcdPriceField1", x), _
"rcdPriceField2", y))

Note that there is no max function as you used it in your
other example. You will have to create your own Public
function to do that. (The expression Service does
understand VBA Public functions.) There is a good one at:
http://allenbrowne.com/func-09.html
 
S

Steve

Ypur price table is incorrect. Typically prices are st up like this:
TblProduct
ProductID
ProductName
<Other product fields as needed>

TblProductPrice
ProductPriceID
ProductID
Price
PriceAsOfDate

To do your calculations, you would create a query that included both tables.
TblProduct is included just to get the product name. If your calculations
are for only one product, you would enter a criteria in ProductID so your
query only returned calculations for one product.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
G

Guest

I was just using the Product table as an example, and I already wrote my own
Max function. Just placing the field names in fields of another table
defeats the purpose of have table driven calculations for me.

The deal is there are over 100 complex calculations and that number may
grow. I was hoping to be able to have the calculations pulled from a table
so when new calculations are introduced, i can simply add some more row and
not have to add more code. I would also be able to make part of the
calculations include the field names I am working with. Here is my table and
example.

Calculations table
- Formula Number
- Calculation Text

1
Max(rcdPriceField1, rcdPriceField2, rcdPriceField3)

2
(Max(rcdPriceField1, rcdPriceField2, rcdPriceField3) - ( 2 * rcdPriceField2))


So could I pull the info above from a table and have the code execute on
it??????? I think not, but I am hoping someone out there smarter than me may
know how.

Thanks again,
Mike P
 
S

Steve

Calculations are done in queries by adding a calculated field to your query.
This is simple if you always have the same price fields. If you need to add
a new calculation to your database, you merely create a new query.

You can use a calculations table like you want but you will find that to be
much more complex. To use a calculations table you need to create a QueryDef
that includes your price fields and a calculated field for your formula.
(Look up QueryDef in the Help file) So in the end of using a calculations
table, you will be using the same query(s) as if you had done the
calculations in query(s). Then, you create code tha modifies the calculated
field according to the Formula Number. You need a form designed to allow you
to select a Formula Number so in your QueryDef you can modify the calculated
field to the Calculation Text that corresponds to the selected Formula
Number. You can do all this through a series of Select Case statements.

Whether you are doing your calculations in queries or through a calculations
table you need to have the correct tables. It still appears your tables are
not correct.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Top