CODEWITHSUNDEEP
ms-accessvba
user3460765
user3460765

Reputation: 21

Access case statement to return value from mutliple fields

I'm struggling to write a case statement in Access VBA to perform what should be the relatively straight forward task of refering to a certain field when a case is met.

Say I have a table witgh ID, Product Type, QTY, Amount, Total

QTY, Amount and Total all mean the same thing, but where this is shown in the table is dependant on the product type. ie if product type is 1 then the 'QTY' field will be populated, if product type = 2 then the 'Amount' field will be populated.

What I'm trying to do is write a function for a query that refers to a specific field for the result and produces something like this:

If Type = 1, then refer to Quantity for the result, If type = 2, then refer to Total for the result etc. I used an iif statement before to refer to the correct column (iif( [MainTable]![Type] = "1",[main table]![Quantity],) but there are too many variables for 'Type' in my real table so this won't do anymore..

Public Function TotalAmount(Type As String)

    Select Case Type

        Case "1"
            TotalAmount = "Quantity"

        Case "2"
            TotalAmount = "Total"

        Case "3"
            TotalAmount = "Amount"

    End Select

I'm pretty new to coding so I've got no idea how to refer to particular field for the result and this is as far as I've got.

Upvotes: 2

Views: 1214

Answers (1)

HansUp
HansUp

Reputation: 97101

Consider a Switch expression in your query to evaluate those cases and return the value of the correct field. (You might not need a custom VBA function for this.)

SELECT
    ID,
    [Product Type],
    Switch
        (
            [Product Type] = '1', [QTY],
            [Product Type] = '2', [Total],
            [Product Type] = '3', [Amount]
        ) As TotalAmount
FROM YourTable;

Note the Switch logic is similar to the Select Case you were using in your VBA function. So I hope this suggestion is reasonably clear. If not clear enough, look at the Switch Function topic in Access' built-in help system.

In a comment, you indicated you want [Amount] as the returned field when [Product Type] is "3", "4", or "5". You can adapt the Switch suggestion to accomplish that ...

    Switch
        (
            [Product Type] = '1', [QTY],
            [Product Type] = '2', [Total],
            [Product Type] In ('3','4','5'), [Amount]
        ) As TotalAmount

Upvotes: 2

Related Questions