MS-Access / Getting Started

Passing data to a function

In most cases, you want your function to accept one or more values that you pass to it as data for the function to operate on. For example, the Sqr() function accepts a single argument, which must be a number. To define the arguments that your custom function accepts, use the following syntax, inside the parentheses that follow the function name:

name As Type

where name is just some name that you make up to use as a placeholder for the incoming value, and Type is a valid data type. For example, you might want the custom SalesTax() function to accept a single numeric value as an argument. You need to make up a name for that, so just call it AnyNum. You also have to define that incoming value as some sort of number. Most likely, the passed value is a Currency value anyway, so you can modify the custom SalesTax() function as follows to accept a single number as an argument:

Public Function SalesTax(AnyNum As Currency)
End Function

What the first line really means is "Expect some number to be here when called. Refer to that number as AnyNum and treat it as a Currency number."

A function can accept any number of arguments. If you want a function to accept multiple arguments, give each argument a name and data type by using the same preceding syntax. Separate each definition with a comma. The SalesTax() function needs to accept only one argument, so don't modify that one. However, just as a general example, if you want a function to accept two arguments, you define each as in this example:

Public Function funcName(AnyNum As Currency, AnyText As String)
End Function

Returning a value from a function

A function can also return a value - that is, only one value because a function can't return multiple values. To make your function return a value, you just add

As Type

where Type is a valid data type, to the end of the first statement, outside the closing parenthesis of the function name. You specify only the data type of the returned value - don't give it a name. For example, you might want the SalesTax() function to return a single value that's a Currency number. In that case, modify the SalesTax() function this way:

Public Function SalesTax(AnyNum As Currency) As Currency
End Function

The custom function doesn't return its value until all the code in the procedure has been executed. To define the value returned by the function, use the syntax

functionName = value

where functionName is the same as the name of the function itself, without the parentheses, and value is the value that you want the function to return (although the value can be an expression that calculates a return value).

Suppose you want to be able to pass to the SalesTax() function some Currency value, like $100.00 or $65.45 or whatever, and have it return the sales tax for that amount. To pick a number out of a hat, the sales tax rate is 6.75 percent. The following SalesTax() function performs the appropriate calculation (by multiplying the number that's passed to it by 0.0675) and then returns the results of that calculation:

Public Function SalesTax(AnyNum As Currency) As Currency
    'Multiply passed value by 6.75% (0.0675) and
    'return the result of that calculation.
    SalesTax = AnyNum * 0.0675
End Function
[Previous] [Contents] [Next]