Storing a Calculation as a string in a table and executing it in a Query.

[KevinM]

N.B. This is not your usual 'Bad practice to Store calculated values in a table' post

I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.

e.g

Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]

So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.

How do I go about this?

TIA

 

[gemma-the-husky]

not sure, but if possible i would tokenise it in some way and try to do it with reverse polish notation

 

[Dennisk]

if you need to calculate strings as expressions look up the EVAL() function.

 

[KevinM]

Thanks

EVAL didn't work.
I found a thread that suggests using vbTextCompare in a function which seems to work ok.

 

[The_Doc_Man]

I agree that this is not a case of storing computed results. You are storing the formula used to compute results, which is a different statement entirely. Unfortunately, it ain't gonna be so easy to reach the goal of arbitrarily executing the string as a formula.

In general, your problem is that tables don't get executed. QUERIES can be executed. Tables are inherently static entites - despite the fact that they can be updated in many ways.

Tables just sort of "lie there" like a blue-tick hound dawg on a hot summer day. Therefore, evaluating expression strings in the context of tables just isn't going to work too well. As of right now, I'll tell you - for the tables - give up EVER seeing the value of that expression while opening a table in datasheet view. That context is totally hopeless for anything indirect like you are trying to do.

However, I never let a little thing like contrary theory stop me so, try this for the other Access entities that AREN'T tables...

If there is a prime key on the record, you could have a query field or a form/report control or a VBA formula that does something like this for its data binding and/or evaluation:

=DLookup( ItemFormula, "tablename", "[PKfieldname]=" & CStr(Value of PK) & """")

You might have to play with the syntax just a bit on ItemFormula. For example, you might need to use """" & [ItemFormula] & """" - I'll leave the experimentation to you. The prime-key selection depends on whether the PK is text or numeric, so you'll have to diddle around with that part, too. 'bout the only part I'm sure of is "tablename" - and you have to supply that yourself.

In order to have ANY CHANCE AT ALL to make this work, there are a few very important "don't" admonitions:

- DON'T spell any of the field names incorrectly.

- DON'T include any quantity in the formula that is neither a field in the same table nor a numeric constant nor a function of one of those two item types.

- DON'T forget the bracketing syntax in the ItemFormula.

- DON'T do this if the table has no good prime key. If the key allows duplicates, THIS WILL FAIL MISERABLY. DLOOKUP will flat-out barf if you are the least bit careless.

- Not an absolute DON'T, but a strong statement: AVOID IF AT ALL POSSIBLE any use of another nested DOMAIN AGGREGATE function. I'm not sure that a DLookup will work correctly if the formula itself contains another domain aggregate. At the very least, the quoting syntax becomes nightmarish. DSum, DMin, and DMax are no better.

Also note that in the table, you still cannot execute the implied DLOOKUP string because in that table, it is still just a static string. So my previously stated limitation still applies.

The more difficult part of this to fathom is that you probably cannot have very many fields to contribute to the formula, so the complexity of your formula is similarly limited. The more fields you have, the more options you will have - but at some point you will probably reach a limit. Worse, at some point you might have to introduce the equivalent of a repeating group for some formula - at which time you cross the normalization line.

I'm not going to say that this basic stored-formula concept inherently violates normalization. But it still is a non-traditional database layout practice. So don't be surprised when (not IF) Access barfs on some of the possible formula strings. You will probably have to experiment a lot to see what your real limits are. HINT: Look up Help on DLookup to see what ITS limitations are. You will inherit them if you try this method.

 

[KevinM]

Thanks for the replies

I managed it with a vb function using REPLACE and EVAL function.

The values will be calculated in a query and NOT stored in a table.
The Formula will always be limited to the said fields.

This is one of those rare tables when the formula IS data and needs to be seen by the user, as each user (staff member) will be have different duties and with each duty comes a 'LOADING' and it is this 'LOADING' that calculates there annual hours (the formula) and is totalled up.
The boss wants to keep an eye on all staff members' total 'loading hours'

 

[The_Doc_Man]

If you have something that works, that is good. Thanks for replying to show how you fixed it. Our newer members can benefit from your explanation. Having a posted solution always helps.

But now that you explained what this was all about, your explanation reveals that this is actually a non-normalized table structure in which the formula is referencing a repeating group (hours for a duty) that appears in a given table.

Consider this alternative...

tblStaff
StaffID, PK, some number
other staff-related data

tblDuties
DutyID, PK, some number
DutyDesc, text description of duty
(?)DutyHours, number, hours per (time unit) associated with this duty UNLESS the amount per staff member is variable for the same duty

tblStaffDuty (a junction table)
StaffID, foreign key to staff member
DutyID, foreign key to duty
(?)DutyHours, number, hours per (time unit) associated with this duty for this staff member UNLESS the duty hours are always the same for each staff member who has that duty.

In other works, the duty hours will either go in the Duties table or the StaffDuty junction table but not both. Depends on the constancy of the hours for a given duty.

NOW your formula is just the sum of duty hours for a given staff member based on joining the staff, duties, and StaffDuty table, group by staff member.

So I now disagree with your original contention:

N.B. This is not your usual 'Bad practice to Store calculated values in a table' post

Actually, it might be very bad practice.

The reason I recognize this is because it is not that dissimilar to a contract-loading matrix we use. Our service representatives are assigned some number of projects to which they can charge. This number of assignments varies from one person to another. We then have to assign more duties to folks until they reach (approximately) 2000 hours per year and everyone is more or less balanced in hour assignments. We have an overhead number that is variable and is used to top off the extra time that cannot be allocated directly.

For instance, I'll have 85% of my hours from project R, 10% from project %, and 5% on the overhead number. The guy sitting next to me in the office might have four projects at 23% each based on allocated hours - and he only needs 8% overhead allocation.

This (to me) sounds similar to what you are doing. And your formula suddenly makes a lot of sense - as an overly cumbersome and unnatural approach to something that should be a LOT simpler.