A function returns a value. Access already has built-in functions such as SUM, which shows the result of adding several numbers, and COUNT, which returns how many values there are. A function can be used in place of a value in an expression for a field in a query, a control source on a form or report, and more. The logic for writing a function is the same as for creating a formula.
Let’s write a function to return the maximum value from a set of two numbers or dates. If one of the pair is missing, then the other should be returned. If neither has a value, the result will be null.
WRITING A FUNCTION
Press Alt+F11 to go to the Visual Basic Editor. Create a new module. From the menu, select Insert, Module. At the top of the new module, it will say “Option Compare Database.” Option statements are instructions that apply to the whole module. This one tells the code how to compare string data. “Option Explicit” can be added to require that all variable names be defined. That way, if your code contains any misspelled variables, the error will be identified when the code is compiled. Otherwise, it could create problems (run-time errors) when the code is used, such as in a function or query. Functions and Subs are defined below the Option statements.
Figure 1 shows a function called GetMax. The first line after the option statements begins with “Function GetMax” to declare a function and define the name. After this are parentheses, which can be empty or contain arguments (parameters). In this case, there are two arguments containing the values to be compared: pvValue1 and pvValue2. Finally, the data type of the return value is specified “As Variant.” Variant provides flexibility for different types of data and to handle a null result. Not only will the GetMax function work for numbers and dates, it will also work for strings. For example, GetMax(5,1) will return a value of 5, GetMax(Date(),#1/1/2010#) will give today’s date, and GetMax("abc","def") will return “def.”
The return value is assigned to the function name. In the code, GetMax=pvValue1 means that the value returned for the GetMax function will be the value pvValue1, which is the first parameter.
Enter the function in Figure 1 into the module. When done, go to the menu and select Debug, Compile to compile the code and see if there are any errors. Once any errors are resolved and everything is correct, save the module. It’s a good idea to use a prefix such as “mod” when naming a module to ensure the module name is different than any name used by any other procedure, such as a function. In this case, since the module only has one function, it will be called “mod_GetMax.”
One place to use the UDF is in a query. For example, in our database for customer payments, the qCustomerBalance query contains separate columns for the last date a customer ordered something and for the last date a payment was made. Using the GetMax function, we can add a column named LastActivity to show the most recent date there was any activity by adding a field that contains LastActivity: GetMax( [MaxDateOrder], [MaxDatePay] ).
UPDATE A TABLE
The Products table of our database has a field called SerialNum that corresponds to the plate on each custom product that the company makes. There are values like 160813-BN-4518-816, 161130-PSLB-5158-1016, and 150830-EBN-2806-810. The first part of the serial number is the manufacture date in YYMMDD format. The second part corresponds to the base model, such as BN, PLSB, or EBN. If you wanted to use the second part to fill a short text field called Model, you could build a function as shown in Figure 2.
Called GetStringPart, the function returns part of a string. It uses the Split function to separate parts of a string into an array of values. Since the first array index is zero (0), one (1) is subtracted from the part number passed. A specific array element is referenced by putting its element number in parentheses following the array name. The third parameter is optional and specifies what delimiter to use for splitting, which by default will be a dash.
GetStringPart can be used in an SQL statement to update the Model field in the Products table with the relevant part of the serial number:
UPDATE PRODUCTS Products
SET Products.Model = GetStringPart( [SerialNum], 2, "-" )
WHERE ( Products.SerialNum Is Not Null );
Functions used in a query must specify all parameters, even the optional ones. Even though the procedure defines the delimiter to be optional (Optional psDeli As String = "-") and defaults to a dash, which is what we want, query expressions must still include the parameter.
Download this month’s database: SF_1803_UDFs.
SF SAYS
User-defined functions are reusable and easier to comprehend than complex formulas.
March 2018
