Creating Your First Function

If you discover that you’re repeatedly using the same expression in forms, reports, or queries, you may want to write a custom function that calculates that expression, and then use the function in place of the expression. For example, suppose that you often need to calculate the date of the first day of the next month (perhaps this is the date that payment is due or that shipments go out). You can calculate this date with the following expression.

= DateSerial(Year(Now), Month(Now) + 1, 1)

However, this complicated expression is easy to mistype. Instead of typing this expression, you could substitute a custom Function procedure that performs this calculation. Writing a Function procedure to perform a calculation has several significant advantages over using the equivalent expression. Using a Function procedure, you can:

Be sure that the calculation is performed the same way every time, without the risk of a typing mistake.
Modify the calculation by changing it in only one place (the module in which the function is defined) rather than in every place the calculation is used.
Perform complex operations, such as If...Then logic or looping, which are difficult or impossible to handle in a simple expression.
Handle errors in ways that you define.
Include comments to document complicated expressions.

This section shows you how to create a simple function that calculates the date of the first day of the next month. You’ll use this function to set the value of the BillingDate text box on the Orders form in the Orders sample application.

If you want to use this function in other forms and reports, you’ll want to create a standard module to store it in. You create a standard module in the same way you create and open other database objects.

Û To create a standard module

In the Database window, click the Modules tab, and then click New.
Microsoft Access displays a new module in the Module window.

Note When you open a new module, Microsoft Access automatically includes two Option statements in the Declarations section, as shown in the preceding illustration. These statements tell Microsoft Access how to sort data when running code and whether to warn you if you don’t declare variables. For more information, search the Help index for “Option.”

Û To create a new function

Below the Option Explicit statement (or any empty line in a module), type Function followed by a space and the name you want to give the function. In this case, name your new function FirstOfNextMonth.
Note It’s a good idea to give your functions relatively short names that describe their purpose or the value they return. Function names can’t contain spaces or punctuation marks. For more information on names in Visual Basic, see “Naming Conventions” later in this chapter.
Press ENTER.
When you press ENTER, Microsoft Access scans your typing, checks it for obvious errors, formats it according to a consistent set of rules for capitalization and spacing, and displays it again. This occurs every time you enter a new line in the Module window. Microsoft Access also adds a blank line and an End Function statement. The End Function statement is always the last line in a function.

Note that Microsoft Access adds a set of parentheses after the name of the function. Use these parentheses to enclose any arguments the function takes, if you decide that the function should take arguments.

Performing Calculations in a Visual Basic Function

You perform calculations in Visual Basic the same way you perform calculations elsewhere in Microsoft Access—by using an expression. The difference is in the way you specify where the result of the expression goes. When you create an expression for a control on a form or for a field in a query, the result of that expression is assigned to that control or that field.

When you perform a calculation in Visual Basic, however, it isn’t obvious where the results should go. You have to explicitly assign a destination to the expression. In the case of a function, you want the result of the calculation to be the value returned by the function, so you assign the calculation to the name of the function.

To make a function return the result of a calculation, add an expression to the function that assigns the calculation to the name of the function. For the FirstOfNextMonth function, you add the following line of code between the Function and End Function statements.

FirstOfNextMonth = DateSerial(Year(Now), Month(Now) + 1, 1)

Compiling Your Procedure

Before you can run a procedure you’ve written, Microsoft Access must compile it. When it compiles a procedure, Microsoft Access makes a final check for errors and converts the procedure into executable format. Because Microsoft Access checks the syntax of each line as you enter it, your procedures compile very quickly.

You don’t have to explicitly compile your procedures. If you’ve written a Function procedure, you can simply use it in an expression. Then, when Microsoft Access evaluates the expression, it makes sure all the functions in the expression have been compiled, compiling any uncompiled functions. If any of those functions use other uncompiled procedures, Microsoft Access compiles those as well, and so on, until it has compiled all the code required for it to evaluate the expression. If Microsoft Access discovers an error at any point during the compilation process, it stops compiling and displays an error message.

Although automatic compiling is convenient, you can encounter error messages when you aren’t expecting them. For example, if you write a function and then use it in a form without compiling it first, you may not discover an error in the function until Microsoft Access attempts to compile it when you try to view data in the form.

To make sure that a procedure has been compiled, you can explicitly compile the code in your database.

Û To compile code in all currently open forms, reports, and modules

On the Debug menu in the Module window, click Compile Loaded Modules.

Microsoft Access compiles all procedures that are in open modules. If it encounters an error, Microsoft Access stops compiling, displays a message, and highlights the line of code that contains the error.

See Also For information on debugging errors in your code, see Chapter 7, “Debugging Visual Basic Code.”

Û To compile all code in the current database

On the Debug menu in the Module window, click Compile All Modules or Compile And Save All Modules.

Microsoft Access compiles all the procedures in the database. This may take time if you have a large number of procedures or modules.

Tip If you click Compile And Save All Modules, Microsoft Access saves all the code in your database in its compiled form. It’s a good idea to save modules after you compile them, because this allows Microsoft Access to run them more quickly when you first open them in the future.

Using Your Function

If you’ve followed the steps in this section, you now have a working function that you can use in an expression almost anywhere in Microsoft Access. You may want to use your new function:

In other Visual Basic procedures that you write.
In the expression that defines a calculated field in a form, report, or query.
In the expression that defines the criteria in a query or the condition in a macro.

The following procedure shows you how to create a calculated text box on the Orders form that shows the billing date of the order. When order takers take a new order, this text box will use the FirstOfNextMonth function to automatically display the first day of the next month as the order’s billing date.

Û To display the result of a function in a calculated text box

Open the Orders form in Design view.
Add an unbound text box to the Orders form, and set its Name property to BillingDate.
Set its ControlSource property to the following expression:
```
=FirstOfNextMonth()
```
Now, when an order taker begins to enter a new order, the BillingDate text box automatically displays the first day of the month that follows the current month, as defined by the computer’s system clock.

Note When you use a function in the property sheet, you need to include the parentheses after the function name. If the function has required arguments, you must include them inside the parentheses. For more information, see the following section, “Supplying Arguments to Your Function.”

Supplying Arguments to Your Function

Functions often take one or more arguments—values that you supply when you call the function and that the function uses to calculate the value it returns. Many of the functions supplied with Microsoft Access take arguments. The functions you write can take arguments as well.

For example, the FirstOfNextMonth function currently returns the first day of the month that follows the current month, as defined by the computer’s system clock. This works fine when an order taker enters a new order, but it’s not what should be displayed in the BillingDate text box for orders that were taken in previous months. Instead, the value in the BillingDate text box should be the first day of the month that follows the value in the OrderDate text box.

You can change the function so that it accepts an argument and then calculates the first day of the month following a date you pass to that argument. You specify the arguments for a function by placing them inside the parentheses that follow the function name.

Function FirstOfNextMonth(dtmAny As Date) As Date
	FirstOfNextMonth = DateSerial(Year(dtmAny), Month(dtmAny) + 1, 1)
End Function

In the ControlSource property box of the BillingDate text box, you pass the function the value in the OrderDate control, so that the function always returns the first day of the month following the month that the order was taken.

Note You can dimension the data type of an argument in the function declaration. You can also (and normally should) dimension the data type for the function's return value. In the example above, the function's return value and the argument dtmAny are both dimensioned as the data type Date.

See Also For information on syntax and naming rules for arguments, search the Help index for “Function statement.”

Adding Comments to Your Procedure

Whenever you create new procedures or modify existing code, it’s a good idea to add comments that describe what the code does. Comments don’t change what your code does, but they help you and other programmers understand it and they make your code considerably easier to maintain.

Each line of a comment begins with an apostrophe ( ' ). This symbol tells Visual Basic to ignore any words that follow on that line. You can enter comments on a line by themselves, as shown in the following code, or at the end of a line of code.

Function FirstOfNextMonth (dtmAny As Date) As Date
	' This function calculates and returns the date of
	' the first day of the month following the date passed by
	' the argument.
	' Note that this works even if Month(dtmAny) = 12.

	FirstOfNextMonth = DateSerial(Year(dtmAny), Month(dtmAny) + 1, 1) 
End Function