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
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.
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".
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.
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
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.
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
(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
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)
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
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...
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
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
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.
try out your Age function click in the Immediate Window and
type (you choose a date*):
... 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
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
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:
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
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
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.
If you want some more ideas for custom functions,
take a look at these tutorials: