Microsoft Access database - Build Custom Functions

Being also a seasoned MS Excel user (I guess, just like you), you’d expect MicrosoftAccess 2016 database ebook 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?

Public Function Age(DOB as Date) as Integer
This will automatically add the closing signature End Function below it.
Age = Int((Date – DOB)/365.25)
which will return a single (whole value – hence Integer) representing the number of years for a date entered.
? Age(“4/8/1970”) and press the Enter key to see the answer.

If you stick to and use Access’s primary applicational feature of data-management even though it does also have a sizeable collection of functions, you still would be better off using functions that belong to Microsoft access database and not have to rely on any external appellations to support it. With Access, this normally means working with Ms access queries. Queries are written in a standard language called SQL (Structured Query Language) which is used by all access 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’.
3. 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).
4. 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: 
5. Now, add the following code in between the two new lines:
6. 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:

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 2016 database ebook 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 training VBA eBook - due out soonJ