The definition of any function no matter the application is the ability to calculate and process passing variables or values to return a single value (the answer).

All we do is simply call the function (by its name) and place some parameters (some are optional) and let the system reveal the answer by return. Where this is used within the context of Microsoft Access can be one of many places including tables, queries, forms and reports.

Custom Access database functions will either be a user writing VBA code compiling routines and other pre-defined Access functions to utilise a tailored function for these objects. Another way to build a custom function is to simply borrow from another application (if it exists) and don’t re-invent the wheel!

The simple steps to building an Access database function

So here’s how to do just that showing you the following simple steps in borrowing other functions in this case from Microsoft Excel.

The example I’m going to show you is to borrow Microsoft Excel’s Proper function which doesn’t exist in Microsoft Access. This function simply returns the initial capitalisation of a word i.e. “access database” to “Access Database“.

1. Create a new module which will take you into the Visual Basic Editor view.

2. From the menu bar, choose Tools and then References… to display the references screen and scroll down for “Microsoft Excel X.0 Object Library” (where X.0 represents your version and in my case 14.0 = version 2010).

3. Add the following VBA code:

Public Function Proper(field As String) As String
   Dim xlf As Excel.WorksheetFunction
   Set xlf = Excel.WorksheetFunction
   Proper = xlf.Proper(field)
End Function

4. Save the changes and you are good to go.

Now in a query, you can call this function which will be listed in the Expression Builder tool under the name of the database in the module name (i.e. Module1 if it were left unnamed).

When you wrap the Proper function around a field i.e. Proper([CompanyName]), it will convert words to initial capitalised letters.

Custom Microsoft Access functions are easy to build and use. See which other Excel functions could serve you well.

Another Tip for you! You may want to build a separate module (give it a name i.e. MyFunctions) and export the module into a ‘bas’ file which can then be distributed and imported to other Microsoft Access databases. Don’t forget to reference the Excel library for each database.

I invite you to keep up to date with my articles and eBooks which covers a lot of details and can be found at http://AccessDatabaseTutorial.com.

From Ben Beitler – “Your Access Database Expert”