VBA calculated field for multiple fields in a query

Question

I have no experience writing VBA code but I can follow the logic.

Thank you for everyone's help in advance!

I found a VBA code that calculates a single field in a query (see pic). Because I have 10 fields I would need to create 10 separate functions: nPP1SURF, nPP2SURF, nPP3SURF ... ending with nPP0SURF.

Can I accomplish this with a single function?

Public Function UpdatePP1SURF(ByVal nPP1SURF As Integer) As String

    If nPP1SURF = 1 Then
        UpdatePP1SURF = "D"
    ElseIf nPP1SURF = 2 Then
        UpdatePP1SURF = "T"
    ElseIf nPP1SURF = 3 Then
        UpdatePP1SURF = "W"
    ElseIf nPP1SURF = 4 Then
        UpdatePP1SURF = "A"
    Else
        UpdatePP1SURF = "x"
    End If

    'Expr1: UpdatePP1SURF([nPP1SURF])

End Function

The logic for all 10 fields is the same.The vba functions I've seen that can accomplish what I want has the word "array" in it.

BRILLIANT! Works great Lee Mac!!

My concern is my other calculated fields are much more complex. Is my original function better suited to handle these IIf statements? For example:

IIf([xPSHF2] Between ([xPSHF_TOP]+1.1) And [xPSHF2]<([xPSHF_TOP]+10),"OR","x")

I greatly appreciate your help Lee Mac!!

I Googled "access select case statement" and I'll begin learning more about this type of coding. I see there is a IF-THEN-ELSE statement.

Cheers!

It may be clearer to use Select Case in place of a chain of If``ElseIf statements. I don't understand why you need 10 separate functions? If each function will use different logic then we don't have enough information; if each function is to use the same logic, then you can just use the one function you have already. — Lee Mac
Edit your original question rather than using the comments section to provide additional information. — Lee Mac

Lee Mac Lee Mac · Accepted Answer · 2018-02-22T00:22:06

Since you have stated that the logic is to be the same for all fields, simply pass each of your fields to the function.

As noted in my comments, I would first suggest using a Select Case statement:

Function UpdateSURF(ByVal mySURF As Integer) As String
    Select Case mySURF
        Case 1: UpdateSURF = "D"
        Case 2: UpdateSURF = "T"
        Case 3: UpdateSURF = "W"
        Case 4: UpdateSURF = "A"
        Case Else: UpdateSURF = "x"
    End Select
End Function

This may then be evaluated for any of your fields using expressions such as:

UpdateSURF([nPP1SURF])
UpdateSURF([nPP2SURF])
...
UpdateSURF([nPP0SURF])

VBA calculated field for multiple fields in a query

1 Answers