All of the database aspects on this site can be used in a variety of database systems and this is intentional. You may recall from the ANSI standard section that most databases support the standard that was created in 1992; and in technology standards, that is ages ago! I graduated from both high school and college and had five kids in that time! Functions are a clear example of how each vendor has decided to implement certain logic and to a degree is a differentiator between the database platforms.
In Microsoft Access there are two types of functions—those you can use for VBA code and those for SQL Queries. We will only deal with those you can use for SQL queries for now.
What is a function?
A function is a little piece of code the database has to help you, the query writer, do routine tasks so you don’t have to write the code each time. There are various types of functions, but they all behave in a certain pattern. While some functions are used outside of a table, it is best to think of a function in terms of a column and what you want to do with the data in the column.
Using a function
Each database will have snippets of code we can access by using the name of the function, and then passing the required information—normally just a single column name—inside of open and closed parentheses. Let’s say, for instance, that we want to choose a name for our new dog and the database has a function we can use called ChooseNewName ( ).
Because our database holds several pet options for us to choose from, we need to specify the type of animal we want to choose a name for. The type of pet we choose will become a parameter—a piece of information the function needs to complete the task. We input (or ‘pass’) the parameter to the function in the parentheses () next to the function name. We will pass a parameter value of ‘Dog’ and our ChooseNewName function will return a name out of all the names in our DogOwner table. Our query would be as follows:
SELECT ChooseNewName('Dog') AS NameOfDog;
(1 row(s) affected)
In certain cases, a function can return a single row, or it could return a row for each qualifying row in the table. Part of using the function is understanding how it will affect our query. This is especially important when we start using our own home-grown functions.
A simple function we can try is to count all the rows in the table. Sure, we could simply select all the rows and see the result, but the database has a function we can use called COUNT. The * specifies that all rows should be counted to return the total number of rows in a table.
SELECT COUNT(*) AS 'Total Number of Cats' FROM Cat;
Total Number of Cats
(1 row(s) affected)
A single row will be returned and the result will be the number of rows in our cat table.
Warning**Do I always have to use a parameter? The answer depends on the function. Some functions may not return a value unless a parameter is given. A simple test—running the function without a parameter to see if you get an error—will let you know if you need to provide a parameter or not.**
SELECT ChooseNewName ();
An insufficient number of arguments were supplied for the procedure or function ChooseNewName.
Want to follow along on your own database?
Do you want to follow along with the examples? We have created a sample database you can use to follow along and try in your own database. Go to the download page and follow the instructions. Have fun on the SQL trail!