CODEWITHSUNDEEP
vbams-access
sloshake
sloshake

Reputation: 13

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!

Upvotes: 1

Views: 478

Answers (2)

June7
June7

Reputation: 21370

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.

Upvotes: 1

June7
June7

Reputation: 21370

Only 3 formulas? Use conditional code in the VBA. Send a card identifier to the procedure as an argument:

Sub testFormulat(strC As String)
Dim result As String

Select Case strC
    Case "Visa"
        result = Mid(Me.Data, 3, 2)
    Case "Mastercard"
        result = Left(Me.Data, 2)
    Case "AMEX"
        result = Right(Me.Data, 2)
End Select

CurrentDb.Execute "Update PayerData set CheckNum = '" & result & "' WHERE ID = " & Me.ID

End Sub

Where will the Data value come from? Is code behind a form? Is form bound to the table result needs to be saved in? If so, the SQL is not needed, simply: Me!PayerData = result

Upvotes: 1

Related Questions