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