Search code examples
vbams-access

Update a MS Access table using a formula stored in another table

I have one table called PayerFormulas that includes a different formula for each payer. Another table is PayerData that has the payer and the data we receive. I want to populate PayerData.CheckNum using the appropriate formula stored in PayerFormulas. The CheckNum data in the table below is what I would like the result to be.

In pseudocode it feels like this is what I'm looking for.

Update PayerData PD
inner join PayerFormulas PF on PD.Payer = PF.Payer
set PD.Check = PF.Formula

I've tried the above in a regular Access query and it doesn't work. Trying the following code only puts the formula text in the table, not the result. I've looked at using Eval() in some way, but since my result will often include text, it doesn't look like the route to go down..

Sub testFormula()

Dim SQLString As String
Dim ActiveQuery As QueryDef

SQLString = "Update PayerData PD inner join PayerFormulas PF on PD.Payer = PF.Payer set PD.CheckNum = PF.Formula"

Set ActiveQuery = CurrentDb.CreateQueryDef("", SQLString)
ActiveQuery.Execute dbFailOnError

End Sub

PayerFormulas table

Payer      | Formula
-----------|--------
Visa       | mid([PayerData].[Data],3,2)
Mastercard | left([PayerData].[Data],2)
Amex       | right([PayerData].[Data],2)

PayerData table

Payer      |  Data  | CheckNum
-----------|--------|---------
Visa       | 123456 | 34
Visa       | ABCDEF | CD
Visa       | qwerty | er
Mastercard | 123456 | 12
Mastercard | ABCDEF | AB
Mastercard | qwerty | qw
Amex       | 123456 | 56
Amex       | ABCDEF | EF
Amex       | qwerty | ty

Thank you for any help!

Solution

  • Okay, to continue with the original example of 3 formulas. Your idea to use Eval() is valid but have to concatenate the variable provided by [Data] field. Modify the formulas table to break up the function parts into separate fields.

    Payer      | Func   | Arg1 | Arg2
-----------|---------------------
Visa       | mid    |  3   | 2
Mastercard | left   |  2   |
Amex       | right  |  2   |

    Then in the query that joins tables:

    CheckNum: Eval([Func] & "('" & [Data] & "', " & [Arg1] & ", " & [Arg2] & ")")

    Note the apostrophe delimiters around [Data].

    Or the args can be in one field and entered as comma separated value: 3, 2. Then:

    CheckNum: Eval([Func] & "('" & [Data] & "', " & [Args] & ")")

    Or, if you really want the formula in one field, enter it as: Mid(PayData,3,2), Left(PayData,2), Right(PayData,2). Then in query calculation, Replace PayData with value of Data field:

    CheckNum: Eval(Replace([Formula], "PayData", "'" & [Data] & "'"))

    BTW, don't really need to save the result to table, calculate when needed.