that you can use to modify the data

that's stored in your data tables.

Occasionally however, these won't be enough

to calculate exactly what you need.

Or perhaps you want to save a complex calculation

to use over and over again.

In these cases, you'll want to create

your own custom functions in Visual Basic.

We can start that process us by coming up to the Create tab

and coming over to the Macros and Code section

on the far right and clicking on Module.

That'll open up a brand new program

called Microsoft Visual Basic for Applications.

And this is where we get the acronym, VBA.

I want to make sure that we have three different windows open

here on our screen.

We've got the Project Explorer, up here on the upper left,

the Properties Window on the lower left

and the Immediate Window across the bottom.

If you're missing any of these windows,

just come up here to View and you can toggle them on

with the Immediate Window option, Project Explorer

or Properties Window.

In the Properties Window,

I'm going to change the name of our module

from the default name to Myfunctions.

You'll see that change is made up here

in the Project Explorer and in the title bar of the window

that's currently open here.

In this window, is where we're going to type in

the different functions that we want to create.

I'll come down to the line below, Option Compare Database

and we'll type in the keywords, Public Function.

Public means that this function is going to be available

outside of the code module.

So, we can use it in queries and forms, for example.

The function that I want to create is going to calculate an age

when we give it a date of birth

I'll name it simply, Age and open a parenthesis.

Inside of the parentheses,

we're going to type in the different arguments

or the data that we're going to pass in to the calculation.

So, in order to calculate an age, we need a date of birth.

I'll just call it DoB for short.

Then, Access needs to know what type of data to expect.

Now, obviously, date of birth is going to be a date data type,

so I'll type in as Date.

We'll finish the Public Function declaration

with a closing parenthesis and press enter

to come down to the next line.

When I do so, Visual Basic adds in

the End Function line down below.

In between these two lines,

is where we're going to type in the different steps,

to calculate an age given a date of birth.

We'll start that process by typing in Age equals.

Now, there's lots of different ways to calculate an age

given a date of birth.

One way is to take today's date

and subtract the date of birth

and that'll give us the number of days that have elapsed

between the two.

In order to get today's date,

I'll use one of Access's built-in functions,

that's simply called, Date.

That'll give us today's date and we'll simply subtract DoB.

This is the date that we pass in, when we run this function.

Now, I want to make sure that this calculation happens first,

so, I'm going to wrap that in parentheses.

Then we'll come to the end

and I'll divide the whole thing by 365.25.

This is the average number of days in a year.

At this point, the function is going to return

a very precise fraction of a year

and I actually want to just drop off the remainder

and return the whole number of years.

So, we're going to wrap this whole thing here

in another function.

I'll type a parenthesis at the very end,

I'll come back to the beginning,

I'll type in another parenthesis

and this function is going to be called Int.

This will essentially just return the whole number of years

that have elapsed without any extra days.

So, there's our entire calculation.

Let's come down here to the immediate window

and we'll test it out.

We'll do that by typing in a question mark,

the name of the function, Age,

I'll open a parenthesis and we'll give it a date.

Remember in Access, we use the date delimiters

of the pound symbols around our dates.

So, I'll type it a pound and then 10 slash 28 slash 1955.

We'll finish it with another pound symbol

and a closing parenthesis.

This is the birth date for Microsoft co-founder, Bill Gates.

And when I press enter,

we'll see that at the time of the recording,

that he is 62 years old.

So, we can see our age function is working,

let's go ahead and create another public function.

I'll come back up here into my window,

I'll press enter to come down to another empty line

and we'll start the process again with Public Function.

This function is going to take in two arguments,

the first name and the last name of a person

and it's going to return a formatted string

where we have last name, comma first initial.

I'll name this function, FormattedName, open a parenthesis

and the first argument is firstName

and we'll be passing that in as a string character type,

I'll type in a comma

and I can type in the second argument, which is lastName

and that'll also be a string data type.

I'll type in a closing parenthesis

and this time we want to specify

that we want this returned as a string.

So, I'll type in as string one more time.

We'll come down to the next line

and type in the calculation here,

so, formatted name is equal to

and it's simply going to return just the last name

and then we'll concatenate that

or join that to the text string,

so, I'll type in an ampersand, a double quote,

we're going to join that to a comma and then a space,

I'll type in another double quote and another ampersand.

We're going to join that string to a Left function,

open a parenthesis.

The Left function will process the first name

and I'll type in a comma and a one.

We'll finish that with a closing parenthesis,

another ampersand, a double quote, a period

and a double quote.

Essentially what we're doing here, is building a string

that'll take the full last name,

join that to a comma and a space,

join that to the first initial of the first name

and join that to a period.

Let's come down to the immediate window again

and I'll click my mouse to insert the cursor

and we'll type in, FormattedName, open a parenthesis.

This prompts me for the first name as a string

and remember, we need to type that in double quotes,

I'll type in "Bill", I'll type in a comma

and then the last name as a string again

in double quotes, "Gates".

I'll type in the closing double quote

and a closing parenthesis and press enter.

And I'm getting a compile error here,

that's actually because I forgot the question mark

at the very beginning.

So, go ahead and type that question mark in, run it again

and it returns it as expected, Gates comma B.

Let's go ahead and save our code module now,

I'll press the disc icon here on the toolbar

and it's going to prompt me to save the module name Myfunction,

then I'll just simply say, okay.

So, that's a quick introduction

on how you can begin to create custom functions

in Visual Basic.

(upbeat music)