eval function

[Guest]

I recently posted a question to the formscoding newsgroup about converting an
Excel s.s. to Access and didn't receive a response so am hoping maybe someone
here can help. I have a form that calculates part price using unbound fields
of various
functions applied to a part's components. I thought I would be able to avoid
making this large number of fields part of the form's query to determine
price by
keeping them unbound form fields and simply storing the final price in the
part dimension table. But now I have two problems - one, if I keep this
scenario I don't know how to recalculate all part prices (a batch update)
when component costs change since not all fields are part of the query, or
two, if I resort to putting all fields in the query (which would make it VERY
large) I don't know how to replace the unbound field on my form that calls
the eval function. Specifically, each model has a different formula for
calculating a certain dimension-e.g., model 3302 uses "[LENGTH]+(2*[ENDS
THICKNESS])+(2*[END_CLEATS THICKNESS])" to get OD_Length, whereas 3350 uses
"[LENGTH]+(2*[ENDS THICKNESS])+(2*[RUNNER STRIPS THICKNESS])"- so I have
those formulas as data in the field OD_Length Formula field and on the form
use eval(eval([OD_Length Formula]) to get that dimension. Putting it into a
query gives me #Error. Help please?!

 

[Guest]

Mary,

Wow...using EVAL like you are is the problem here. The Microsoft JET SQL
engine has no way of dealing with an eval() function.

The first method that springs to mind to do something like this could be
rather involved, (and probably run pretty slow) and you would likely be
better off redesigning this application to store the prices directly in the
data and have code re-calculate the price based upon the forumlas you have
stored if and when the record's parameter values get changed.
but... here's what something that could work might look like:

You would have to wind up doing some extensive re-writing of *all* those
eval methods in VBA code.

VBA skeleton code: (module myProceCalcModule)
public Function MyPriceCalculator(CalcID, RecID) as Currency
Dim curRtn as currency
'code here to open the recordset and retrieve the Part Record so you have
access to the part specificaiton field data (i.e. length, flange width,
etc.,.)

Select Case CalcID
Case CalcID1
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion (which might look
something like:
ors.fields("Length").value * 0.023 + ors.fields("Flange Width").value *
0.075 * (ors.fields("length").value * 0.10) )
Case CalcID2
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion
Case CaclIDn (repeat as many time as needed)
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion

MyPriceCalculator = curRtn
'don't forget to close the recordset you used before exiting here

End Function

You could then put some sort of CalcualtionID on the part numbers and in the
code perform the correct calculation based upon the CalculationID... Then you
could have a query expression like MyPriceField:
modules!myProceCalcModule!MyPriceCalculator([CalculationID], [RecordID]) to
try and return a meaningful result for the Price field in the query...

Hope this helps!

I recently posted a question to the formscoding newsgroup about converting an
Excel s.s. to Access and didn't receive a response so am hoping maybe someone
here can help. I have a form that calculates part price using unbound fields
of various
functions applied to a part's components. I thought I would be able to avoid
making this large number of fields part of the form's query to determine
price by
keeping them unbound form fields and simply storing the final price in the
part dimension table. But now I have two problems - one, if I keep this
scenario I don't know how to recalculate all part prices (a batch update)
when component costs change since not all fields are part of the query, or
two, if I resort to putting all fields in the query (which would make it VERY
large) I don't know how to replace the unbound field on my form that calls
the eval function. Specifically, each model has a different formula for
calculating a certain dimension-e.g., model 3302 uses "[LENGTH]+(2*[ENDS
THICKNESS])+(2*[END_CLEATS THICKNESS])" to get OD_Length, whereas 3350 uses
"[LENGTH]+(2*[ENDS THICKNESS])+(2*[RUNNER STRIPS THICKNESS])"- so I have
those formulas as data in the field OD_Length Formula field and on the form
use eval(eval([OD_Length Formula]) to get that dimension. Putting it into a
query gives me #Error. Help please?!

 

[Guest]

Mary,

Know what? I'm - at least partially, all wet here.
With Access 2003, there is indeed an eval() function in Access VB
see:http://msdn.microsoft.com/library/d...y/en-us/vbaac11/html/acfctEval_HV05186386.asp

If you use an EVAL expression like: "= EVAL([MyFormulaFieldNameHere])" as
the datasource for a control, you may get pretty much what you are looking
for here.

This is pretty dern cool, but good luck dealing with errors in the forumlae
passed into the eval function...

Mary,

Wow...using EVAL like you are is the problem here. The Microsoft JET SQL
engine has no way of dealing with an eval() function.

The first method that springs to mind to do something like this could be
rather involved, (and probably run pretty slow) and you would likely be
better off redesigning this application to store the prices directly in the
data and have code re-calculate the price based upon the forumlas you have
stored if and when the record's parameter values get changed.
but... here's what something that could work might look like:

You would have to wind up doing some extensive re-writing of *all* those
eval methods in VBA code.

VBA skeleton code: (module myProceCalcModule)
public Function MyPriceCalculator(CalcID, RecID) as Currency
Dim curRtn as currency
'code here to open the recordset and retrieve the Part Record so you have
access to the part specificaiton field data (i.e. length, flange width,
etc.,.)

Select Case CalcID
Case CalcID1
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion (which might look
something like:
ors.fields("Length").value * 0.023 + ors.fields("Flange Width").value *
0.075 * (ors.fields("length").value * 0.10) )
Case CalcID2
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion
Case CaclIDn (repeat as many time as needed)
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion

MyPriceCalculator = curRtn
'don't forget to close the recordset you used before exiting here

End Function

You could then put some sort of CalcualtionID on the part numbers and in the
code perform the correct calculation based upon the CalculationID... Then you
could have a query expression like MyPriceField:
modules!myProceCalcModule!MyPriceCalculator([CalculationID], [RecordID]) to
try and return a meaningful result for the Price field in the query...

Hope this helps!

I recently posted a question to the formscoding newsgroup about converting an
Excel s.s. to Access and didn't receive a response so am hoping maybe someone
here can help. I have a form that calculates part price using unbound fields
of various
functions applied to a part's components. I thought I would be able to avoid
making this large number of fields part of the form's query to determine
price by
keeping them unbound form fields and simply storing the final price in the
part dimension table. But now I have two problems - one, if I keep this
scenario I don't know how to recalculate all part prices (a batch update)
when component costs change since not all fields are part of the query, or
two, if I resort to putting all fields in the query (which would make it VERY
large) I don't know how to replace the unbound field on my form that calls
the eval function. Specifically, each model has a different formula for
calculating a certain dimension-e.g., model 3302 uses "[LENGTH]+(2*[ENDS
THICKNESS])+(2*[END_CLEATS THICKNESS])" to get OD_Length, whereas 3350 uses
"[LENGTH]+(2*[ENDS THICKNESS])+(2*[RUNNER STRIPS THICKNESS])"- so I have
those formulas as data in the field OD_Length Formula field and on the form
use eval(eval([OD_Length Formula]) to get that dimension. Putting it into a
query gives me #Error. Help please?!

 

[Guest]

Thanks, Mark - as I mentioned, the eval function works fine when I'm
calculating the price via a form with unbound fields (and MUCH faster, I
might add). Where I get the error is if I try to put it into the query which
would replace all the unbound fields and allow me to recalculate all prices
by simply running the query. I've gotten around it with a function that
evaluates the formulas with a SELECT CASE statement, but I'm going to only
use the huge queries when all parts need repricing due to component cost
changes and stick to my original pricing form when it is done one at a time.
Appreciate your assistance.

Mary,

Know what? I'm - at least partially, all wet here.
With Access 2003, there is indeed an eval() function in Access VBA
see:http://msdn.microsoft.com/library/d...y/en-us/vbaac11/html/acfctEval_HV05186386.asp

If you use an EVAL expression like: "= EVAL([MyFormulaFieldNameHere])" as
the datasource for a control, you may get pretty much what you are looking
for here.

This is pretty dern cool, but good luck dealing with errors in the forumlae
passed into the eval function...

Mary,

Wow...using EVAL like you are is the problem here. The Microsoft JET SQL
engine has no way of dealing with an eval() function.

The first method that springs to mind to do something like this could be
rather involved, (and probably run pretty slow) and you would likely be
better off redesigning this application to store the prices directly in the
data and have code re-calculate the price based upon the forumlas you have
stored if and when the record's parameter values get changed.
but... here's what something that could work might look like:

You would have to wind up doing some extensive re-writing of *all* those
eval methods in VBA code.

VBA skeleton code: (module myProceCalcModule)
public Function MyPriceCalculator(CalcID, RecID) as Currency
Dim curRtn as currency
'code here to open the recordset and retrieve the Part Record so you have
access to the part specificaiton field data (i.e. length, flange width,
etc.,.)

Select Case CalcID
Case CalcID1
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion (which might look
something like:
ors.fields("Length").value * 0.023 + ors.fields("Flange Width").value *
0.075 * (ors.fields("length").value * 0.10) )
Case CalcID2
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion
Case CaclIDn (repeat as many time as needed)
curRtn = 'code here to implement the calculation method using the
record's field data as parameterd for the calculaltion

MyPriceCalculator = curRtn
'don't forget to close the recordset you used before exiting here

End Function

You could then put some sort of CalcualtionID on the part numbers and in the
code perform the correct calculation based upon the CalculationID... Then you
could have a query expression like MyPriceField:
modules!myProceCalcModule!MyPriceCalculator([CalculationID], [RecordID]) to
try and return a meaningful result for the Price field in the query...

Hope this helps!

I recently posted a question to the formscoding newsgroup about converting an
Excel s.s. to Access and didn't receive a response so am hoping maybe someone
here can help. I have a form that calculates part price using unbound fields
of various
functions applied to a part's components. I thought I would be able to avoid
making this large number of fields part of the form's query to determine
price by
keeping them unbound form fields and simply storing the final price in the
part dimension table. But now I have two problems - one, if I keep this
scenario I don't know how to recalculate all part prices (a batch update)
when component costs change since not all fields are part of the query, or
two, if I resort to putting all fields in the query (which would make it VERY
large) I don't know how to replace the unbound field on my form that calls
the eval function. Specifically, each model has a different formula for
calculating a certain dimension-e.g., model 3302 uses "[LENGTH]+(2*[ENDS
THICKNESS])+(2*[END_CLEATS THICKNESS])" to get OD_Length, whereas 3350 uses
"[LENGTH]+(2*[ENDS THICKNESS])+(2*[RUNNER STRIPS THICKNESS])"- so I have
those formulas as data in the field OD_Length Formula field and on the form
use eval(eval([OD_Length Formula]) to get that dimension. Putting it into a
query gives me #Error. Help please?!