[AccessD] Need help with Eval() function

Mcgillivray, Donald [ITS] donald.a.Mcgillivray at mail.sprint.com
Tue Sep 9 17:12:11 CDT 2003


Well, thanks for the input, Charlotte, but I already tried that to no
avail.

Since this appears to be a dead end, maybe you or somebody else has an
idea that will accomplish what I'm trying to do.

I have a table of material types that may be added to over time.  I want
the cost calculation to be dynamic, such that when a new material type
is added, the user can define the formula that will deliver its cost.  I
intended to build a wizard-type form that would guide the user through
the creation of the formula by allowing the selection of fields and
operators.  I wanted to store the formula string in the material types
table and use it to drive the cost calculation when called.  My main
objective is to avoid having to open up some custom function to insert
new rules if a material type is added that doesn't fit an existing cost
formula; I want this to be hands-off for me once I turn it over.

Am I dreaming? Or is there another smarter way to do this?

Don

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com] 
Sent: Tuesday, September 09, 2003 1:36 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Need help with Eval() function


Eval doesn't know what to do with a string unless it contains a function
name, or a control reference, or a calculation like your literal value.
If you pass it something like CalcFormula, it interprets that as a
literal.  Try Eval(tblVolume.CalcFormula) and see if that works.  I
can't guarantee it will because Eval is a fairly stupid function.

Charlotte Foust  

-----Original Message-----
From: Steve Capistrant [mailto:scapistrant at symphonyinfo.com] 
Sent: Tuesday, September 09, 2003 12:28 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Need help with Eval() function


I don't know the answer.  I've only used it for functions, especially
when calling functions whose names are stored on a table.

Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
Phone: 612-333-1311
www.symphonyinfo.com
212 3rd Ave N, Ste 404
Minneapolis, MN 55401


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mcgillivray,
Donald [ITS]
Sent: Tuesday, September 09, 2003 1:00 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Need help with Eval() function


Thanks for the reply, Steve!

CalcFormula isn't a function, it's simply a field in the query.  Are you
saying that I can't pass a simple string value to Eval() - that it has
to be a function that returns a string?  If that's so, why, when I pass
the literal contents of CalcFormula, does it return a valid result?

Don

-----Original Message-----
From: Steve Capistrant [mailto:scapistrant at symphonyinfo.com]
Sent: Tuesday, September 09, 2003 10:28 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Need help with Eval() function


I think Eval requires that you include the parentheses of the function
you call.

E.g,  Eval(CalcFormula())

Also, make sure that CalcFormula is Public and a Function (not a Sub)


Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
Phone: 612-333-1311
www.symphonyinfo.com
212 3rd Ave N, Ste 404
Minneapolis, MN 55401


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mcgillivray,
Donald [ITS]
Sent: Tuesday, September 09, 2003 11:34 AM
To: AccessD
Subject: [AccessD] Need help with Eval() function


Hello, All

I need to calculate the cost of material for a series of products, each
of which has a number of different material components.  The cost of
each material component is calculated differently depending on what it
is.  For example, outside envelope cost is a function of the number of
mail pieces multiplied by the unit cost of the envelope; form cost is a
function of the number of pages multiplied by the unit cost of a single
form; toner cost is a function of the number of print feet multiplied by
the cost of toner per print foot; and so on.

I have constructed a query that delivers a row for each material type
used in each product.  Simplified, it looks something like this:

ProdID	Pieces	Pages		Printfeet	MatlTyp	UnitCost
CalcFormula
========================================================================
====================================
1		200		500		480		Envelope
.0135		tblVolume.Pieces * tblMaterial.UnitCost
1		200		500		480		Form
.0015		tblVolume.Pages * tblMaterial.UnitCost
1		200		500		480		Toner
.000025	tblVolume.Printfeet * tblMaterial.UnitCost
2		5000		20000		18000		Envelope
.0142		tblVolume.Pieces * tblMaterial.UnitCost
2		5000		20000		18000		Form
.0102		tblVolume.Pages * tblMaterial.UnitCost
2		5000		20000		18000		Toner
.000025	tblVolume.Printfeet * tblMaterial.UnitCost

And so on . . . .

The CalcFormula string comes from a table that is keyed to the material
type with the formulas stored as text values.

I am trying to use the Eval() function to deliver a total cost for each
row.  Using Eval(CalcFormula) results in an error.  However, if I paste
the literal value of CalcFormula into the Eval() function
(Eval(tblVolume.Pieces * tblMaterial.UnitCost), for example), I get a
valid result.  Now, it seems to me that if the literal value of the
CalcFormula field can be made to work with the Eval() function, then I
ought to be able to pass a reference to a text field containing that
value and get the desired result as well.  Obviously, I'm missing
something about using this function.  Can anybody steer me down the
correct path?

Thanks!

Don McGillivray
Sprint Mailing Services _______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list