Build Custom Functions for Your Access Applications

Why Build Custom Functions?

If you came to Access after using Excel you may have noticed that Access seems a little short on calculating power. Many users get this first impression of Access. Excel is principally a tool for calculating, whereas the prime function of Access is the storage and retrieval of data.
Excel has a vast library of built-in functions. Access also has a sizeable collection of functions but smaller than that of Excel. One reason for this is that much of the calculation done in Access is by means of queries. Queries are written in a standard language called SQL (Structured Query Language) which is used by all database programs. Access's built-in functions have to be compatible with SQL, which tends to limit its calculating power.
Sooner or later most Access users find that Access doesn't have a built-in function to do the calculation they want. What can you do?
Once again VBA comes to the rescue. If Access doesn't have the function you need, you could try writing one of your own. This tutorial shows you how to build and use a custom function and shows you how to use it in forms and queries. You will often see custom functions referred to as UDFs (User-Defined Functions).
The tutorial is suitable for Access versions 97, 2000 and XP (2002). Microsoft introduced the Visual Basic Editor, a separate program that runs from within Access, with Access 2000. In some places I have provided separate instructions for Access 97.

Creating a New Custom Function

Step 1: Open a New Module

The first step is to open a new code module. A module is simply a container for a collection of VBA code.
Move to the Modules section of the Access Database Window and click the [New] button. Access 97 opens a new code module window (you will notice that the toolbar and menus change to provide the appropriate VBA editing tools). In Access 2000/XP the Visual Basic Editor window opens with a new module named Module1.

If you are working with Access 2000/XP you should make sure that you can see the Project Explorer pane and the Properties pane on the left of the Visual Basic Editor window. You can open them from the View menu if necessary.

It is a good idea to give your module a meaningful name. You can keep all your code in one module or organise it in different ones - it's up to you. I have spent so much time searching through modules named Module1, Module2 etc. trying to find a particular piece of code that I usually rename mine. In Access 2000/XP you can rename a module from the properties pane. Click on the module name in the Project Explorer pane, then change "Module1" in the Properties pane to your chosen name (usual object naming rules... no spaces!). This one will contain functions so I've named it "Functions".

If you are working in Access 97 you can name now by clicking the Save button and typing your chosen name in the dialog box. Alternatively you can wait until you close and save your finished module, when you will be prompted for a name.  

Step 2: Enter a Name for the Function

I'll start with an easy one!  This function calculates a person's age from their date of birth. In the code window type: 

   Public Function Age(DoB As Date)

... and press ENTER. When you do this the program automatically enters the closing line End Function and places your cursor in the space between.

What does it mean?

• The keyword Public makes the function available outside its host module. This means you can make use of it in other modules, and elsewhere in Access (e.g. in queries, forms and reports).
• The name Age is the one you will use when addressing the function. Try to make your function names descriptive but keep them short. Function names can not have spaces, so separate words with underscores or capitals, e.g. Age_in_years or AgeInYears.
• (DoB As Date) declares the function's arguments. An "argument" is a piece of external information that the function needs to do its calculation. In this case there is a single argument DoB, which has to be a date.

Step 3: Enter the Function Code

This function is quite simple and requires just one line of code. Type the following line in the space between the Public Function and End Function statements:

   Age = Int((Date-DoB)/365.25)

What does it mean?

• Date-DoB uses the VBA function Date which always returns the current date (like Excel's TODAY() function) and subtracts from it the supplied date of birth (DoB). Windows treats dates as numbers based on the "1900 System" where day 1 was January 1 1900. So, subtracting the date of birth from the current date gives the person's age in days.
  
  Supposing today was January 20 2002 (day 37276) and I was born on September 27 1950 (day 18533) I would be 37276-18533 or 18743 days old today.
• (Date-DoB)/365.25 divides the result by the average number of days in a year (365.25). The brackets ensure that the sum is done before division. The result is the person's age in years.
  
  To continue the example, that makes me 18743/365.25 or 51.3155373 years old today.
• When considering someone's age we are usually interested only in whole years so I have used the Int() function to convert the calculation to a whole number (i.e. an integer) which it does by rounding down.
  
  So my age today is 51 (I know, but I've had a hard life!).

Your finished code should look like this:

Public Function Age(DoB As Date)
   Age = Int((Date - DoB) / 365.25)
End Function

Step 4: Check Your Code

You should always check your code before you use it for real. There is a quick and easy way to check for errors in syntax, undeclared variables, and typos that the VB editor didn't spot when you were typing your code. This process, called "compiling" the code, effectively carries out a "dummy-run" of the code without actually changing any data.

To compile your code module go to Debug > Compile (Database Name). If there are any problems the compiler will point them out. It's up to you do figure out what's wrong and to put it right (i.e. to "debug" your code). Here the compiler has found a word that it doesn't recognise. It turns out to be a typo, Dste instead of Date...

If you find any errors, fix them and compile again. If nothing happens when you compile, it means that no errors were found. If you check the Debug menu you will find the Compile command is greyed-out...

Step 5: Test Your Function

You can often test functions without leaving the VB editor, by making use of the Immediate Window. The purpose of the Immediate Window is to allow you to try out code by entering it directly into the window, or by having your code write its results directly to the Immediate Window using the Debug.Print command.

In Access 2000/XP go to View > Immediate Window (keys: CTRL+G). A separate pane labelled Immediate appears at the bottom of the code editing window.

In Access 97 go to View > Debug Window. A new window opens, the lower section of which is the Immediate pane.

To try out your Age function click in the Immediate Window and type (you choose a date*):

   ?Age("27/09/50")

... and press Enter. Access will calculate the function and show you the result in the line below. As you type the functions arguments are displayed as a pop-up tip...

   

*NOTE: Working with dates in VBA can be confusing. When referring to dates in code VBA will expect you to use the mm/dd/yy system. Here, however, the function is behaving as if it were taking the date from a field in a table, so it expects you to use your computer's default system. My example shows the date in dd/mm/yy format because I work in the UK and that is how I normally enter dates.

Using Your Custom Function in a Form

You can use a custom function in the same way as you would a built-in function. Forms and reports work the same way. Here's how to add a text box displaying a person's age on a form:

Step 1: Draw an Unbound Text Box

In design view, draw a new text box on your form using the Text Box tool. This is an "unbound" text box because it is not linked to a field in the form's underlying table. In design view the text box currently displays the word "unbound" but in form view it appears blank...

Step 2: Enter the Function into the Text Box

In form design view click in the unbound text box and enter the function text as follows:

=Age([DoB])

Note that here I have entered the name of the field that contains the person's Date of Birth. I usually use the abbreviation "DoB" when referring to date of birth. Don't be confused by the fact that I also named my function's argument "DoB". You should enter the appropriate fieldname is square brackets. For example, if your Date of Birth field is called "Birthdate" then you would type =Age([Birthdate]).

You can also enter the function by typing it into the Control Source section of the text box's Properties Window like this...

TIP: When you have a calculated field on a form, help your users by setting that field's Tab Stop property to No. You'll find the Tab Stop property in the Other category of the text box's properties window. This will exclude the text box from the form's tab order, so that the user will not visit it when tabbing through the form. It also locks the text box so that the user can not type over the calculated result.

Step3: Save the Changes and View the Form

Save your changes and switch the form into form view. Your new text box will display the result of the calculation that the function performs. This data is not stored anywhere. The function calculates it as the form displays each record...

Using Your Custom Function in a Query

It is very easy to perform a calculation using a query. Create a new query in design view and choose the field that you want to display. To add a new, calculated field type a name for the field, followed by a colon (:) in the top (field) row of an empty column of the query design grid. In the example below I have used the name "Age" (Note: You must not use the name of an existing field.)

After the colon type the function expression. You don't need to type an equals sign (=). As in the form example above, my field containing the Date of Birth data is called "DoB". You can choose whether or not to display this field. The function will work whether the query displays the source data or not. I have chosen to leave it out...

Run the query to display the function's results...

Where Else Can You Use Your Custom Functions?

Custom functions can be used in Access reports in the same way as in forms, by creating an unbound text box in the design view of the report and entering the function expression as you would on a form.

You can also make use of your custom functions in any VBA procedures you write. Many VBA functions are interchangeable between Access and Excel, although they might need slight modification to suit the host program.

Sumber: http://www.fontstuff.com/vba/vbatut04.htm