Microsoft Access Tips for Serious Users

Provided by Allen Browne

Returning more than one value from a function

A function can only have one return value. In Access 2, there were a couple of ways to work around this limitation:

VBA (Access 95 onwards) allows you to return an entire structure of values. In database terms, this is analogous to returning an entire record rather than a single field. For example, imagine an accounting database that needs to summarize income by the categories Wages, Dividends, and Other. VBA allows you to declare a user-defined type to handle this structure:

    Public Type Income
        Wages As Currency
        Dividends As Currency
        Other As Currency
        Total As Currency
    End Type

You can now use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:

    Function GetIncome() As Income
        GetIncome.Wages = 950
        GetIncome.Dividends = 570
        GetIncome.Other = 52
        GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
    End Function

To use the function, you could type into the Immediate Window:


(Note: the use of "Public" in the Type declaration gives it sufficient scope.)

Programmers with a background in C will instantly recognize the possibilities now that user-defined types can be returned from functions. If you're keen, user-defined types can even be based on other user-defined types.

Home Index of tips Top