Subtitles section Play video
- [Adam Wilbert] Access shifts with many built-in functions
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)