Microsoft access database – build custom functions

Being also a seasoned MS Excel user (I guess, just like you), you’d expect Microsoft Access database functions to provide just a rich level of pre-defined functions one could adopt. However, you will have noticed that this is not the case.

MS Access seems a little short on the richness with regards to the calculating power, defaulting back to using Exceland leaving Access to get on with what it does best – storing and the retrieval of data.

Did you know that you can actually use Excel functions in your Access database? With a little bit of VBA code, you can by setting a reference to the spreadsheet application.

Why Build Microsoft Access Custom Functions?

If you stick to and use Access’s primary applicational feature of data-managementeven though it doesalso have a sizeable collection of functions, you still would be better off using functions that belong to MS Access and not have to rely on any external appellations to support it. With Access, this normally meansworking with 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 it turn, can limit its calculating power.

So, you need to rely on some (but basic) VBA code to come to the rescue. If Access doesn’t have the function,then you need to build a custom one.

UDF’s (stands for User Defined Functions) is the term referred as custom-built functions and can be applied to most versions as far back Access 97 (yep, last century!).

The steps may be really easy, but the coding is the challenge (for the non-programmer though).

1. First, you need to add a module where your code is to be stored and then called. If you are new to this, take a quick look at the tools here.
2. I would give it a meaningful name like ‘Custom Functions’ and save your module something like ‘mod_CustomFunctions’.

1. Optionally (and recommended), If the two keywords ‘Option Explicit’ do not appear at the top of the module, then type it into the module (below way it should say Option CompareDatabase).

1. Now, to create a function called Age where you will supply a valid date-of-birth value (as a date/time value as its parameter), you create a signature:Public Function Age(DOB as Date) as Integer

   This will automatically add the closing signature End Function below it.

1. Now, add the following code in between the two new lines:Age = Int((Date – DOB)/365.25)which will return a single (whole value – hence Integer) representing the number of years for a date entered.

1. Save you changes and now you can test this out. In the Visual Basic editor view (where you have just created your custom function), press Ctrl + G to open the Immediate window (if not already opened). Now type the following:? Age("4/8/1970") and press the Enter key to see the answer.

You can also create a form, report or even a query and call this function like any built-in function in Access. Just remember, that this function is stored in one database file and other Microsoft Access database files will also need to have access (and the file must remain open and available). You may have to create a better and more global way to make this accessible but that’s another season in another blog.

In the meantime, keep an eye out for my up and coming Microsoft Access database training VBA eBook – due out soonJ

MS access is a great database tool and it has great built-in functions to help you create a database, explore more.

About Author: Access Database Tutorial is the leading website is about learning to build an Access database using the most effective techniques. In this Articles it talks about Microsoft access.To know more visit at https://www.accessdatabasetutorial.com/

My name is Ben Beitler (based in London, UK) and I have been building, consulting & training with Microsoft Access databases for over 20 years.