Results 1 to 9 of 9

20050420, 01:11 #1
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Storing formulas in tables (2002)
Hi,
I'm starting to build a new database that will have set formulas applied to accounts based on number of years in business. The formulas are used to calculate franchise fees. I'm wondering if there is a way to store these formulas in a table to be used as a look up table later on. My goal would be to have a query that multiples the net amount for the month by the formula, vs having to type the formula into the query. I've been able to get it to work if I simply enter a number into the table, IE [year one] = .15, [year two] = .25 etc. I set the value of the field as a number and this works fine. However if I get more complex as it =.15+100 and change the format to text then try to us it in the query I get an error message. I assume because its text vs number. So, before I spend hours playing with this I thought I'd ask if its even possible to do.
Thanks,
Leesha

20050420, 06:42 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Storing formulas in tables (2002)
You could use the Eval function, but that won't make your query easier to enter or to understand. If you want to perform more complex calculations, consider writing a VBA function.

20050420, 11:44 #3
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Storing formulas in tables (2002)
Hi Hans!
I'll have to read up on the Eval function as I've not used this one yet. I'm laughing as it never occured to me to write a VBA function as I've become so used to using queries to generate most of what I want. I'm amazed at the power of them! It doesn't help that writing VBA functions is not my strong suit as this forum well knows and so I find I live in the world of queries these days. I guess being able to only use code to open forms and reports isn't sufficient huh?
Off to experiment,
Leesha

20050420, 11:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Storing formulas in tables (2002)
I didn't mean that you shouldn't use a query. You can use VBA functions in queries. Some calculations become very cumbersome if you try to do them using only standard expressions; a custom function can simplify things considerably.
It is not very clear to me yet what exactly you want to accomplish; if you provide more details, someone may be able to help.

20050420, 12:02 #5
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Storing formulas in tables (2002)
So much to learn! It never ends but it so much fun!
The user has a variety of formulas (I haven't seen them yet) that are used to calculate the royalty payments for a group of franchises. These formulas will change over time. My goal was to have them be able to make adjustments to the formulas and store these in a table so that they don't have to enter the formula each time they generate the royalty bill. They would only change them when they need to be changed. My goal is then to have the bills generate using a query that looks for the corresponding formula that is stored in the table to determine the royalty fee vs having me have to recode the query every time their formulas change.
Leesha

20050420, 12:08 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Storing formulas in tables (2002)
Problem is, how are the users going to enter the formulas? A formula such as =.15+100 (from the first post in this thread) is meaningless if you don't know what should be done with it: if you take it literally, the result of this formula is always 100.15, but that was probably not its intention. Access does not have builtin "artificial intelligence" to guess the user's intentions, so chances are that a userentered formula will either result in an error or in an unintended value.

20050420, 12:24 #7
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Storing formulas in tables (2002)
Well I guess I'll have to forgive Access for not having built in intelligence since it does so many other things so nicely.
My thought was to have the query take the field from the table that has the formula IE .5+100 in it and multipy it against the field that contains the dollar amount. So if there are two tables, tblSales and tblformulas in a query, I'd have something like this:
Royalty:[tblsales].[salesgenerated]*[tblformulas].[firstyearroyaltyfee]..................where [firstyearroyaltyfee] actually contains the formula. You are right, I get error messages when I try this so that is why I'm wondering if its even possible to do or if there is another approach that would be better before I start building.
See Hans, I am learning to ask the question as to approach BEFORE I set up something that is a nightmare to fix.
Slow learner,
Leesha

20050420, 13:04 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Storing formulas in tables (2002)
I'm sorry, but the "stored formula" idea isn't going to work, for a variety of reasons.
Are the formulas always of the same form, for example, multiply the value by a factor and then add an amount? If so, it could be done by storing the factor and the amount in a table instead of the formula, more or less like this (WARNING: air code):
Royalty:[tblsales].[salesgenerated]*[tblformulas].[factor]+[tblformulas].[amount]

20050420, 13:47 #9
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Storing formulas in tables (2002)
Hi!
Your approach will work as far as I can tell. My female brain keeps making things harder than they need to be.
Thanks for the simplification!
Leesha