We haven’t had an Access post in a while, so today, I’ll show Access some love.
We all know that we can create our own User Defined Functions in Excel. Well, did you know you can also create and use your own User Defined Functions in Access?
Creating Your User Defined Functions
Just as you would in Excel, simply enter your function into a standard module. In this example, I placed this small function called LastDayThisWeek into a standard Module. This particular function gives me the last day of the week.
If you have lots of User Defined Functions to keep track of, you can create one module to hold them all. For example, I typically put all my user defined functions into a module called My_User Defined_Functions. This makes finding and editing your functions easy.
In addition, when I activate the Expression Builder, I can drill into the My_User Defined_Functions module to see a complete list of my user defined functions.
Using Your User Defined Functions
In a query environment, you can use your User Defined functions in the same ways you would use built-in Access functions. In this example, I’m using my FirstDayLastMonth function.
In an Access Form, you can tie the Control Source for a text box to one of your User Defined functions (this same method works in Access reports).
In this example, this form will automatically execute the FirstDayLastMonth function each time it is opened to provide a value to the assigned text box.
And of course, your User Defined Functions can be used in other VBA procedures.
This procedure uses the FirstDayLastMonth function to find the first day of last month and then puts that date into a message box.
The problem with UDF’s is if you want to pull data from a query that has a UDF in to Excel – the ODBC driver does not recognize it and will give you an error – Unrecognized “some function” in query
In fact some of Built in functions like Replace also throw up this error.
last day of this month is: DateSerial(Year([date]),Month([date])+1,0) not DateSerial(Year([date]),Month([date]),0)
Hi
I am trying to make a userdefined function as per your instructions but I can not see the module or function in express builder do you know why.
Justin