Roz Clarke
roz.clarke at donnslaw.co.uk
Wed Sep 10 03:04:15 CDT 2003
Forwarded due to auto-rejection by the mail server, which I will
investigate!
Roz
-----Original Message-----
From: Liz Doering [mailto:ldoering at symphonyinfo.com]
Sent: 10 September 2003 05:09
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Need help with Eval() function
Don,
This idea might be useful:
I once built an app that allowed users to build queries on the fly and store
them for future use in searching a database of recipes. There was a wizard
that allowed users to type in, for example, Chocolate, choose an operator
(OR or AND), then add more terms and operators until they were satisfied.
(Chocolate or Strawberry or Butterscotch). They could also choose the f
ields to be searched (RecipeTitle AND Ingredients). When they were
satisfied, I added on, in code, the pieces to make a complete SQL string
(SELECT * FROM Recipe WHERE RecipeTitle="chocolate" Or
RecipeTitle="strawberry" Or RecipeTitle="butterscotch" OR
Ingredients="chocolate" Or Ingredients= "strawberry" Or
Ingredients="butterscotch"). They named their query ("Ice Cream Toppings"),
and I stored the entire SQL string in a table with the name of the query and
a PK of QueryID.
When it came time to run their stored query for a future search or report,
they could pick from a list of stored queries (their names), and I could
look up the stored string based on QueryID and run the whole thing with a
db.Execute(strSQL).
In your case, you might not give them so much freedom to store any thing at
all, but a user could be taught to select from field names in a list and
operators in a list. You could cobble up the code behind the scenes and
store a whole string ("SELECT [CalorieCount]/[Servings] AS
CaloriesPerServing FROM Recipe"). You would need to design your GUI fairly
carefully to prevent them making truly outlandish strings, but if the
results were weird, they would be able to go back and change them without
you.
Your function CalcFormula would then have one parameter (QueryID). It would
open the query aa a recordset, then return only the calculated value you
wanted (CaloriesPerServing).
Hope this is helpful and not too late!
Liz Doering
Symphony Information Services
liz at symphonyinfo.com
Phone: 612-333-1311
www.symphonyinfo.com
212 3rd Ave N, Ste 404
Minneapolis, MN 55401
---------- Original Message ----------------------------------
From: "Mcgillivray, Donald [ITS]" <donald.a.Mcgillivray at mail.sprint.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
Date: Tue, 9 Sep 2003 17:12:11 -0500
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
Liz Doering
Symphony Information Services
612.333.1311
--