1 of 2

Write now Article Tools

In general a top down approach should be followed especially when creating a function.

Start with a module, if need be create a new module. From the database Window, click on the Modules tab. Click the New button. From there click for the Insert menu, which is between the View and Debug menus on the top bar. Click Procedure and we are on our way.

You will be given a choice between a Sub, Function or Property. Specifically, a function is designed to return a value. Many programmers use Sub(routine) and Functions interchangeably but depending on the platform there can be issues with this. For ease of use, select a Public function. This will make it available for execution outside of the module. Please put a name for the function and try to use some sort of a naming convention. For myself, all my functions usually begin with fct or fnc.

The first and last line of the function is then inserted for you. The rest is up to you. If you wish the function to return a value, you would put the terms " AS xxx" after the close bracket, where xxx is the variable type of the returned value. If you want the function to be passed a value for processing you would put a variable name and data type inside the brackets. In general, the object reference ByVal is assumed. This means that if you pass the function another variable, the function will be performed on the VALUE of that variable. If you wish the variable itself to change value, you must use the ByRef object reference. The other object reference to keep in mind, is Optional which will allow the function to continue even if no value is passed to it.

Ensure that somewhere in the code there is a line that sets the function to the value that you want returned ie functioname = value. Luckily, the MS Access help routines provide examples for everything discussed here. All in all, this is a very powerful method to perform data processing activities within your database.

Learn more about this author, Gail Wynand.
Click here to send this author comments or questions.

Add your voice

Know something about How to create a custom function in Microsoft Access?
We want to hear your view. Write now!