CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
Jake
Jake

Reputation: 913

Get VBA to Evaluate Formula In String

I have a table that stores a string representing a formula that I would like to have either Access or VBA evaluate. A few example strings look like:

Basically, I'm trying to change how different names can be viewed based on entity type, missing information, etc.

Is there any way to force either Access (in a query) or VBA (as part of a custom function) to return what the string is telling it as opposed to the literal value? From the examples above, I would expect:

Replace by itself won't work, as some of the formatting includes LEFT(table.Name,1) or other functions. I'm just hoping there is a simple way to force the string to be evaluated, rather than having to come up with a complex function.

I apologize if I haven't explained this well, I feel like my attempt to merge the database aspect with the string formatting aspect may not come across clearly. If you have questions please reply and I'll do my best to explain it better.

Thank you in advance.

Upvotes: 1

Views: 2529

Answers (3)

4dmonster
4dmonster

Reputation: 3031

As you have a special table with formulas you can aim at queries and/or VBA.

  1. For VBA you can store code for eval function to be run in form of several forms with same set of required fields(that is used in formulas): in field VBAformula of your special table should resides such string me![LastName] & ', ' & me!FirstName & ' ' & LEFT(Me![Middle],1) then in some function you could write:

    formula = dlookup("VBAformula","SpecialTable","id=" & me![HowToViewID])

    Me![ViewAs] = eval(formula)

  2. For Queries you can store SQL formula to be used in UPDATE query, SQLformula with such string: [LastName] & ", " & [FirstName] & " " & LEFT([Middle],1) then you can build and run SQL:

    formula = dlookup("SQLformula","SpecialTable","id=" & me![HowToViewID])

    currentdb.Execute "UPDATE [table with people] SET [ViewAs] = " & formula & " WHERE peopleID=" & Me![peopleID] & ";" , dbFailOnError

These are examples to show some variants, so there is no error handling or security.

Upvotes: 0

Gustav
Gustav

Reputation: 55961

You could build an SQL string from your table formulas like:

SQL = "Select " & tblFormulas.FormulaField.Value & " From " & Split(tblFormulas.FormulaField.Value, ".")(0) & ";"

Not bullet-proof but a start ...

Upvotes: 0

mwolfe02
mwolfe02

Reputation: 24227

How about the Microsoft Access Eval() function:

?Eval("3 + 1")
4

Upvotes: 1

Related Questions