CODEWITHSUNDEEP
ms-accessvba
liamthequietman
liamthequietman

Reputation: 11

VBA calculated field for multiple fields in a query

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!

Upvotes: 1

Views: 141

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

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])

Upvotes: 0

Related Questions