Using Custom Functions in Calculated Controls

Back in May 2012, I wrote a blog article on Writing Custom Functions for Access VBA.  Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers.  We do this by creating a public function with the VBA programming language and save it inside a global module within the database.

The blog I wrote in 2012 explained how these custom functions can be accessed from sub procedures associated with forms and reports elsewhere in the database.  Today, however, I am going to explain how custom functions can be utilised in calculated form controls, without needing to invoke them with VBA itself.

Let's begin by looking at the custom function we are going to use.

Custom Function to get Week Commencing

I have created a custom function called getWeekCommencing to calculate the week commencing date of a given week, in a given year.  For example, the week commencing date of the 14th week of 2019 is 1st April 2019 (if we count Monday as the first day of the week). As such the function takes two arguments - week number and year.  The full syntax for using the function is as follows:

getWeekCommencing(weekNumber, Year)

The code I used to create this function is as follows:

Public Function getWeekCommencing(intWeekNo, intYear)
On Error GoTo myError
    Dim intJan1 As Integer
    Dim varStartDate As Variant  
    intJan1 = Weekday(DateSerial(intYear, 1, 1), vbMonday)
    varStartDate = DateAdd("d", -(intJan1 - 1), DateSerial(intYear, 1, 1))
    getWeekCommencing = DateAdd("ww", intWeekNo - 1, varStartDate)  
leave:
    Exit Function
myError:
    MsgBox Error$
    Resume leave  
End Function

If you are unfamiliar with VBA you can still copy this code into your database and use it within a calculated control. There are instructions for doing this below:

Instructions for adding the function to your database.

1.  Copy the code above onto your clipboard.
2.  Open the database you are going to add the function to.
3.  Select the CREATE ribbon.

   

   Above: The MODULE icon is located on the right-hand side of the CREATE ribbon.

   
   
4. Click the MODULE icon from the MACROS AND CODE group.  This opens the VBA editor and creates a new module ready for the code to be added:
5. Paste the code into the VBA editor as in the screenshot below:

Above: The VBA Editor:

You may notice that I have changed the default name of the Module (see the PROJECT EXPLORER in the 

left-hand side bar) from Module 3 to "myFunctions".  You can do the same by highlighting the module name in the 

project explorer and hitting the F4 button to bring up the PROPERTIES window. You may then change the name property

 to one of your choice.  However, this is not essential.

The custom function is now ready to use in a calculated form control. 

Setting up the Test Data for this Exercise

Before we create the form to test the function, we first need to set up a simple database table containing test data. The table needs just two fields: WeekOfYear and Year, both of which are of the NUMBER data type.

Above: tblWeeks in Design View

Above: tblWeeks in Form View with test data entered.

Once the table is set up, we can add our test data as I have done in the screenshot above. Although there are  52 week in the year, the first week may not start on a Monday, so the data entered in WeekOfYear field can be anything between 1 and 53 (so the possibility of a partial first week is offset by Week 53).

The Calculated Control

Now we have added the getWeekCommencing function along with test data to our database, we can create the form containing the calculated control.

Above: The Week Commencing Calculator form in Design View.

To do this we need to create a form bound to the tblWeeks table.  We then need to add three text boxes:  the first text box (txtWeekOfYear) is bound to the WeekOfYear field in tblWeeks, and the second (txtYear) is bound to the Year field.  The third text box (txtWeekCommencing) is unbound.  This is where we are going to add our calculated control which returns the week commencing date (based on the data displayed in the WeekOfYear and Year textboxes).

Here are the instructions for creating the calculated control:

1. Create the third text box that we are going to use for the calculated control as in the screenshot above. 
2. Display the PROPERTY SHEET if it is not already visibile.  The PROPERTY SHEET icon is located in the TOOLS group of the DESIGN ribbon.
3. Select the new text box by clicking on it.
4. Click the OTHER tab of the PROPERTY SHEET, and change the NAME property to txtWeekCommencing. (I also suggest you change the names of the first textbox to txtWeekOfYear and the second to txtYear by selecting them and changing the NAME properties).
5. Click the DATA tab on the PROPERTY SHEET (having first selected txtWeekCommencing).  We are now going to enter an expression which uses our getWeekCommencing custom function.  
6.  Enter the expression below directly into the CONTROL SOURCE property:

=getWeekCommencing([txtWeekNumber],[txtYear])

The txtWeekNumber parameter references the value in the txtWeekNumber textbox, and the txtYear parameter does the same for the txtYear text box.  

In this exercise, the expression was entered directly into the CONTROL SOURCE

property.  Alternatively, we could have clicked the elipse button at the end of the CONTROL SOURCE
property row to open the EXPRESSION BUILDER.  Using the Expresison Builder is, however, something 

I would need to cover another day, in another post!

And that's it! All you need to do now is save the form and open it in FORM VIEW.  Use the form's navigation buttons to view each record in the tblWeeks table.  The calculated control uses the getWeekCommencing custom function to processes the week number and year data for the current tblWeeks record, and displays the appropriate date.

NB - Just to clarify, the format for the week commencing date in this screenshot is for the UK (ie dd/mm/yyyy). 

The same date using the US format would be 01/07/2019 (mm/dd/yyyy). However, your computer should be set up
to display the date format appropriate for your location automatically.