John W Colby
jwcolby at gmail.com
Wed Mar 20 22:16:06 CDT 2013
Does anyone know a way to implement "dynamic" math equations in Access? I am implementing a system for building up strings of verbiage for mail merge letters. The verbiage is dependent on the insurer of a loan and the state that the loan is in. For example it might say something like "Please Bid $X." X is the result of an equation that may be something like X = the greater of FMV or MakeWhole but X must be at least 2/3 TotalDebt. Or it may say something like X = Y% * FMV or TotalDebt whichever is less, where Y% comes from the insurer table, i.e. Y is 80% for insurer A, 90% for insurerB and 100a% for InsurerC. The equations can depend on the state but also a % figure taken from the insurer. There are 51 states and 5 insurers so there are a ton of possible combinations / equations. All of which comes from a spreradsheet of text "descriptions" for each insurer for each state which I am supposed to somehow compute. The old system just used a slew of hard coded equations in huge iif() statements, embedded directly in fields in queries. ICK! I would prefer to somehow map this to a small(er) set of equations with values fed in from the state and insurer tables. The verbiage strings would be stored in the state table, possibly a state/insurer table. where the verbiage is in the table with replaceable symbols in the string. I could pull the string out of the table, look for X and run a math function to figure out X and substitute the literal X (or other "replaceable character" ) with some dollar amount. And finally I would like to avoid VBA code if possible. The intent is to eventually move these out of Access so if the solution mapped easily into C# that would be good. I have never really seen anything like this implemented (table driven) and I am drawing a blank on how to go about it, particularly without resorting to custom VBA functions. In the end VBA functions are preferable to IIF() statements in custom Access queries. I could at least "port" VBA to C# later. -- John W. Colby Reality is what refuses to go away when you do not believe in it