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.

Converting Minutes to Hours:Minutes in MS Access

There may be occasions when we need to convert the difference between a start time and an end time, and display the result in hours and minutes.  For example, suppose we set up an Access table field to record the time a person starts work (the session_start field) and another to record when s/he finishes (the session_end) field.  Rather than set up a third field to store the total time worked, we can instead calculate this value automatically using a calculated control in an Access form, or a calculated field in an Access query.

Above: the bottom text box (txtHoursWorked) is a calculated control. 
The CONTROL SOURCE is an expression based on the values contained in the top two text boxes
(bound to the session_start and session_end fields in the undelying table).

However, to do this we must use two expressions to calculate the time difference, and then display the time difference in the hrs:mm format.  So if we enter 20:40 as the session_start, and 22:06 for session_end, we need an expression to calculate the total minutes worked (which in this case is 86 minutes), and then convert the total minutes to hours/minutes format (which would be 01:26).  To achieve this we use the DateDiff and FormatDateTime functions.  Lets take a look at each function individually before combing them to produce the required result.

The syntax for the DateDiff function is as follows:

DateDiff("interval", time_1, time_2)

The function works by calculating the time difference between the time_1 and time_2 parameters, and returns the result in accordance with the interval parameter.  In our case, we want need the result returned as minutes, so enter "n" as interval.   Time_1 and Time_2 correspond to the start time and end time.  To get these values for the expression, we can we put a reference to the values contained in the text boxes for "Start time" and "End time".  In our example these text boxes are called txtStartTime and txtEndTime.  This what our DateDiff function looks once we add the parameter values:

DateDiff("n", txtStartTime, txtEndTime)

The value returned in our example is 86 minutes.

The second function that we are going to use is FormatDateTime which converts the value returned by the DateDiff function into hours and minutes.  The syntax we need to use in the FormatDateTime is as follows:

FormatDateTime( (minutes/(24*60) , named_format)

This first parameter is a division of the number of minutes (returned in the DiffFunction) by the total number of minutes in a day -  which in our example is 86/(24*60) - and returns the result in accordance with the value contained in the named_format parameter.  The latter is a numeric value indicating the required time format - which in our example is vbShortTime ie hours:minutes.  The numeric value we enter for the vbShortTime format is 4.  This is what our FormatDateTime function looks like once we add the parameter values:

FormatDateTime((86/(24*60) , 4)

The value returned from this expression is 01:26 - ie 1 hour and 26 minutes.

When we come to enter the actual expression used as the control source for the TOTAL (HRS:MINS) text box (referred to as txtHoursWorked in the screenshot below), we need to combine these two functions by nesting the DateDiff function inside the first parameter of the FormatDateTime function. This is done as follows:

FormatDateTime((DateDiff("n", txtStartTime, txtEndTime/(24*60) , 4)

Finally, we just need to enter this expression into the CONTROL SOURCE property of the
txtHoursWorked text box (see screen shot below):

The txtHoursWorked text box is highlighted on the left, and the expression used as the CONTROL SOURCE property value is highlighted on the right.

Using the DSum Function

Today we are going to look at using the DSum Function within a Calculated Control to produce a Total Amount. The DSum Function works in a similar way to the DLookUp function which I wrote about in my last post. Both functions pass an identical set of parameters in their syntax. However, instead of looking up a field value in a table or query, the DSum Function calculates the sum of values contained in a particular field of a specified table.

So lets take a look at the syntax of the DSum Function:

=DSum("fieldName", "tableName", "criteria")

The first parameter we pass is the name of the field containing the group of values that we wish to add together as a sum.  The second parameter is the name of the table or query, and the third parameter is an optional criteria used to restrict the group of records upon which the calculation is to be made.  All the parameters are passed as strings - hence the quotation marks.

So, for example, if we had a field called Amount, and a table called tblCategories, our expression would look like this:

=DSum("Amount", "tblCategories", "fldCategory='A'")

The final parameter is the criteria string. In this example the criteria restricts the calculation to records containing the value 'A' in the fldCategory field.  As such this function would produce a Total Amount for all records allocated the category value 'A'.

So lets put all this into practice.  In figure one below I have created the tblCategories table and populated it with values.  There are four different categories A,B,C and D.  Each instance of a category has an amount value associated with it.

Figure 1: The Categories Table (tblCategories)

I then created a form with four Calculated Controls.  I used the DSum Function in each control to calculate a Total Amount for each category.

Figure 2: The DSum Function within Calculated Form Controls.

And here are the results:

Figure 2: Results of the DSum Function.

Using the DLookUp function

Imagine the following scenario: we have created a form to display information contained in an order details table.  Having selected tblOrderDetails as the form's Record Source, we realise this table (part of a Many to Many Relationship) does not include the name of the product item as one of its fields; it instead uses the item name Id as a foreign key from tblProducts.  Obviously, this is going to be a problem from the perspective of user friendliness.  That is to say, somebody using the form is not necessarily going to know which product ID relates to which product item name.  This is where the DLookUp function comes in handy.

The DLookUp function allows the Access Developer to look up the value of a field from a table other than the form's actual Record Source. It is often used as a function in a Calculated Text Box Control (see previous post for more information about Calculated Controls).  So applied to our scenario, we can use a Calculated Control containing the DLookUp function to obtain the item name from tblProducts (based on our knowledge of the product item's ID).

When we use the DLookUp function we need to provide it with three pieces of information (called parameters).  These are:

1. The Field Name
2. The Table or Query Name
3. The Criteria to find the particular record.

The syntax for the DLookUp function used in a Calculated Control is as follows:

=DLookUp("FieldName", "TableName", "Criteria")

In our scenario we would enter the parameters which we need to pass into the text box's Control Source as follows:

=DLookUp("itemName", "tblProducts", "ID = " & forms![frmOrderDetails]![ProductId])

So here we are looking up the value of the itemName field, in the tblProducts table, where the ID for the product record matches the ProductId displayed on our active Order Details form.

It might be worth elaborating on the criteria parameter that we have used.  All parameters used in the DLookUp function (including the criteria parameter), are of the String Data Type.  The criteria parameter is a string containing information similar to an SQL WHERE Clause - except the "WHERE" part of the statement is omitted.  For example "ProductId = 1" instead of "WHERE ProductId =1".  In our example the ProductId used in this expression is going to be different for each current record displayed on the form.  Therefore our criteria needs to refer to the value of the ProductId displayed for the current record on our active form.  This is why our criteria is written:

 "ID = " & forms![frmOrderDetails]![productId]

Notice that only the "ID = " is contained within the quotation marks used to identify a string. The & symbol that appears immediately afterwards indicates that the information following it is intended to be part of that same string - a concatenation.  The criteria ends with the reference to the value contained in the productID of the active Order Details form that we have been working with - the syntax for the reference being forms![frmOrderDetails]![productId].

Calculated Controls

As well as being easy to use, Calculated Controls can be a really useful tool for the Access Developer. They provide a flexible way to display data on form's, without being restricted to information directly derived from a  field in a table or query.  We are all familiar with the simple Text Box Control.  Ordinarily these are bound to a particular field defined in the Text Box's Control Source - ie the control's property that links the Text Box with the particular field that supplies its data.  Calculated Control's, however, are slightly different.  Rather than using a field from a table or query to supply the information displayed in the control, we instead enter an expression into the text box control source.

Figure 1 (above): This is the property sheet for a Calculated Control.
The CONTROL SOURCE is located on the top line of the DATA TAB.

Figure 2: This is how the Calculate Text Box Control appears in FORM DESIGN VIEW.
Notice how the expression is displayed in the text box itself.  All expressions
begin the the = sign, and may consist of operators, identifiers, constants and functions.

The expression I have entered in Figure 1  is used to perform a mathematical calculation.  It works by multiplying the values contained in two bound text box's on the same form in order to produce a Total Amount in the calculated control. In this example I have been able to work out the total value of an order item based on the Unit Cost and Quantity Ordered.  Here is the finished result:

Figure 3: The Calculated Text Box Control multiplies
the UnitCost by Quantity to produce a Total.

The procedure for setting up a Calculated Control such as this is really quite easy.  It is useful, however, to have some knowledge of creating and modifying forms in DESIGN VIEW.  This is how I created the Calculated Text Box Control:

1. I began with an existing form called frmOrderDetails.  The form was bound to a table called tblOrderDetails.  The form began with 4 text box's displaying the ProductId, OrderId, UnitCost, and Quantity fields.
2. The form was opened in DESIGN VIEW.  This was done by right-clicking the frmOrderDetails form, and selecting the Design View Icon from the drop down menu which opened.
3. An unbound Text box Control was added by clicking the TEXT BOX icon and positioning the control on the form design grid.  The TEXT BOX Icon is located on the CONTROLS group of the DESIGN ribbon.  I had to make sure the USE CONTROL WIZARDS icon was not highlighted before doing so.
4. I then highlighted the new unbound Text Box Control and clicked the PROPERTY SHEET icon on the TOOLS group of the DESIGN RIBBON.
5. I needed to select the DATA tab on the newly opened PROPERTY SHEET.
6. I then entered the expression =[unitcost]*[quantity] into the CONTROL SOURCE property.  Unitcost was a reference to the bound UnitCost  text box, and Quantity was a reference to the bound Quanty text box.  These were the expression's Identifiers, and the * symbol was it's multiplication operator. NB I could have typed this expression directly into the text box on the DESIGN GRID - it would have set the CONTROL SOURCE property without having to open the PROPERTY SHEET.
7. Then when I opened the form and entered values in the two bound fields of UnitCost and Quantity, the Total Amount appeared automatically in the calculated text box control.