Convert string to formula (2 Viewers)

[hardy1976]

Hi,

Can you convert a string stored in a field into a formula?

I need to evaluate a number of things with different formulas eg.

Field1 * Field2
Field3 + Field4
(Field5+Field1)/Field5 and so on

I'd like to store the formulas in a field and then run a query to evaluate the answer.

Make sense?

Thanks

 

[Ranman256]

You do in a query.
Put the math in a field in the query and run query.

 

[hardy1976]

You do in a query.
Put the math in a field in the query and run query.

Thanks if I do that I get the textual values rather than the answer... eg
Field1 * Field2
Field3 + Field4
(Field5+Field1)/Field5

and not (for example)

10
20
4

 

[sneuberg]

I suggest using the expression builder. In the query designer place your cursor in the grid where you want the formula (expression), right click and select Build. Here's more info on building expressions

 

[hardy1976]

Will expression builder will do this?

I was thinking function, however I doesnt convert the string into a formula

eg.

One	Two	three	four	Calc
12	32	1		=[one]+[two]/[three]
12	12	3	2	=[one]/[four]

 

[The_Doc_Man]

If you do this in SQL, it might look like this:

SELECT A, B, C, D, A + (B/C) AS Expr1, A/D AS Expr2 FROM MyTable ;

Of course, if C or D is ever 0 or null or blank, you will get 'divide by zero' errors unless you include some error trapping methods such as IIF functions.

 

[HiTechCoach]

Hi,

Can you convert a string stored in a field into a formula?

I need to evaluate a number of things with different formulas eg.

Field1 * Field2
Field3 + Field4
(Field5+Field1)/Field5 and so on

I'd like to store the formulas in a field and then run a query to evaluate the answer.

Make sense?

Thanks

Makes sense to me.

I do something similar. I store form references, function calls, and other things that I use to automate tasks.

It should be possible to create a User Defined Function (UDF) to do it.

There may be a simpler way. Try the Eval() function.

Example:

? Eval("5 + 4")
 9

I have not tried it with exactly what you want to do, but Eval() just might work.

 

[CJ_London]

can you provide the context of what you are trying to do. The short answer to your question is 'probably yes', But all depends on the what, where and how - in a query? on a form or report? using values in a recordsource? another table? another form? How do you want to store the 'text formula'? in a lookup table? hardcoded in VBA? as a calculated value in a query?

I'd like to store the formulas in a field and then run a query to evaluate the answer

this is sort of understandable, but the question is why? since you would run a query to get the answer, you would just put it in a query.

Is the idea that it can be used by a user to create their own formulae?

 

[hardy1976]

Hi, I'm trying to work out an invoice.

So... lots of fields that record data.

I was to use the above to data and use it to multiply/divide/substract etc depending on the field against a core list of item costs.

Make sense?

So table 1 (eg)
field 1
field 2
field 3

table 2 (eg)
field 4 (item cost), field1 * field4 (calc formula)
field 5 (total cost), field2 + field5 (calc formula)
field 6 (item cost), field3 * field6 (calc formula)

Hope that makes sense.

 

[CJ_London]

Hope that makes sense.

not really - as described, I can't see why you wouldn't just put the formula in the query, or as a controlsource of a control in a report or form.

But to answer you question, you could have a table that contain formula as text in a field. if you know the names of the fields, you use those, if you don't you would use a generic 'field1', field2' naming convention.

Then in your main query you would rename fields as required e.g.

field1:Quantity
field2: price

then in VBA you would combine the text of the query with the text of your formula table in a way to make valid sql which you would the either execute or create a new querydef using that sql.

 

[hardy1976]

Hi - thanks errr I'll try again.

Table1 - Rates / Calc
Football - $100 / session.footballs * football
Goals - $30 / IIf((session.goals * goal)<$100,$100,$200)
Teacher - $50 / session.teachers * session.hours * teacher

Table2 - Session
Session Number: 1
Footballs: 2
Goals: 0
Teachers: 1
Hours: 3

Does that help?

 

[Ranman256]

100 / session.footballs * football as Foot-ball

You can't name a field if it already uses that name in it.

 

[CJ_London]

It doesn't help me solve your problem, you are hardcoding values, they should be in a separate table.

conclusion: You are trying to resolve a simple problem in a really complex way and quite frankly I am not prepared to spend time going in the wrong direction.

Explain clearly and briefly what you are trying to do together with your current table structure and relationships and I can suggest a possible revised table structure and the calculation in a query.

 

[HiTechCoach]

Hi - thanks errr I'll try again.

Table1 - Rates / Calc
Football - $100 / session.footballs * football
Goals - $30 / IIf((session.goals * goal)<$100,$100,$200)
Teacher - $50 / session.teachers * session.hours * teacher

Table2 - Session
Session Number: 1
Footballs: 2
Goals: 0
Teachers: 1
Hours: 3

Does that help?

Huh?

I am feeling the same as CJ_London. :banghead:

I have been designing and building Accounting system for 30+ years. I specialize in what customers think are difficult inventory control systems, which includes costing and/or pricing models.

I think you first issue could be with your table structure/design. Your current design is probably causing this to become overly difficult.

Referring the your example above:

Does the Session table (table 2) have 5 records? If not then that is part of the issue.

 

[The_Doc_Man]

This problem suddenly jumps out at me as an Excel approach being translated to an Access environment without understanding the difference between the two.

You are describing the problem in terms of formulas. Screw formulas. What are you trying to do (using English action verbs and such to answer the question)?

 

[HiTechCoach]

This problem suddenly jumps out at me as an Excel approach being translated to an Access environment without understanding the difference between the two.

You are describing the problem in terms of formulas. Screw formulas. What are you trying to do (using English action verbs and such to answer the question)?

Yup ... It sure seams like they want to write a formula in a field thinking it is like a cell in an Excel worksheet.

 

[sneuberg]

So if the question is how to implement Excel type formulas in Access what's the answer? Couldn't this could be done by creating an interpreter? Are there any tools like Lex and Yaac available in VBA? Is there an easier way than creating an interpreter?

 

[sneuberg]

Not that I'm advocating this approach as a solution but if the formulas are written with the field names in brackets it simplifies a bad solution to the problem. In the attached database I scan through the formula looking for the field names by extract the substrings that start and end with brackets. For each field name found, I replace it with the value from the record set. When all of the field names have been replaced by their values I applied the Eval function to the formula.

This would need a lot of work to make it robust and there certainly wouldn't be anyway to make it faster compared to the normal way to solve this nebulous problem.

 

[CJ_London]

So if the question is how to implement Excel type formulas in Access what's the answer?

you could use the excel object.

 

[HiTechCoach]

I like hearing what users think Computers, Operating Systems, and software should work. This has led to inspirations on how to make my software better.

At first glance what hardy1976 wanted didn’t seem that unreasonable. I had use the Eval() function in the past to process actions stored in a table. Never formulas for a record.

My curiosity got too me so I did a little testing on this also.

To make this work you are basically reinventing Excel with Access. Making a database (Access) work like a spreadsheet (Excel) indicates that you are not using the best tool to create the solution.

In Visual Studios I have a custom control that works very similar to Excel. Even has formulas. It is great for adding spreadsheet functionality to an application. It is not very useful for data entry forms with a database.

Creating a solution for what hardy1976 needs in Access is not really that difficult when done using the best practices for Access. Very little coding should be required. It will take some effort to properly design the tables. With the proper tables, everything else gets much easier.