Locked lesson.

Upgrade

About this lesson

Use an expression to perform calculations on fields and display new information, such as an end date.

Quick reference

Calculations Using Expressions

Use the Expression Builder to create a simple calculation. 

When to use

Create an expression whenever you want to perform a calculation on numeric fields in a table. 

Instructions

Display in the subform the date that the SLA expires (End Date). The EndDate information is not available in any table, but we can calculate this using the StartDate field and the SLA field. 

  • Open the form ‘frmRelatedTickets’ subform.  
  • Switch to Design View. 
  • From the Form Design tab, in the Controls group, select the Text Box control. 
  • Draw a text box on the subform. 
  • From the Form Design tab, in the Tools group, click Property Sheet. 
  • Click on the control label and adjust the properties as follows:
    • Name: EndDateLabel
    • Caption: End Date
  • Click on the Unbound field. 
    • Name: EndDate
  • In the Control Source field, click the three dots to open the Expression Builder. 
  • Double-click on the StartDate Field. 
  • Add the + symbol. 
  • Double-click on the SLA Field. 
  • Click OK. 
  • Switch to Form View to check the calculation is working correctly. 
  • Close the Form and Save the changes. 
  • Open ‘frmTicketMaintenance’ and check you can see the ‘EndDate’ field in the subform.
Login to download
  • 00:04 In this section of the course, we're going to take a look at expressions.
  • 00:07 And more importantly,
  • 00:09 how we can use expressions to perform calculations in our databases.
  • 00:13 Now, if we reopen a TicketMaintenance form, notice at the bottom,
  • 00:18 we added those RelatedTickets.
  • 00:20 Now it might be that I want to display in this subform effectively,
  • 00:26 the date when the SLA expires.
  • 00:29 So for this first related ticket it has date logged, and
  • 00:32 then we have the SLA on the end.
  • 00:34 So I effectively want to create an end date.
  • 00:38 Now, I don't have end date information in any of my tables, so
  • 00:42 it's not available for me to just add as an End Date field.
  • 00:46 But what I could do here is I could take the DateLogged field and
  • 00:50 then add the number of days in the SLA column.
  • 00:53 And that will effectively give me an end date.
  • 00:56 So to do this, we're going to need to create an expression and
  • 01:00 perform a calculation, effectively date logged plus SLA.
  • 01:04 Now the first things you really need to think about here are,
  • 01:07 where are the fields that you need to perform this calculation available?
  • 01:11 Which tables are they in?
  • 01:13 Now, both of the fields I'm going to be using are in the RelatedTickets form.
  • 01:18 That's the form that is bound to this subform.
  • 01:22 However, it might be that you have fields in different tables that you want to use
  • 01:26 in your calculations, and that's perfectly fine as well.
  • 01:29 So the first thing we need to do here is we need to add
  • 01:33 an End Date field to a subform.
  • 01:36 So let's double click to open the RelatedTickets form.
  • 01:40 Now we're going to jump straight into Design View,
  • 01:43 and let's open up the Property Sheet.
  • 01:46 Now if we look over in the Record Source,
  • 01:49 we can see that this form is bound to the table tblRelatedTickets.
  • 01:53 If I go to Add Existing Fields,
  • 01:55 I can see all of the fields that are available in that table.
  • 01:59 And both of the fields that I need to perform the calculation are already in
  • 02:04 here, so DateLogged and SLA.
  • 02:06 If you want to grab fields from other tables, you could click on it Show all
  • 02:10 the tables and you can grab your fields from down here.
  • 02:13 So what we need to do is make a little bit of room for our End Date field.
  • 02:19 So I'm going to drag the form along and
  • 02:21 we're going to add another little text box just here.
  • 02:25 Now I know that these text boxes, but if you're ever not sure what type of
  • 02:30 control you have in use in your form, if you click on one of them and
  • 02:34 go to Property Sheet, you can see at the top it says Text Box.
  • 02:38 So let's go up to our controls and
  • 02:41 we're going to grab this first control just here, Text Box.
  • 02:45 Let's drag and make it a similar size.
  • 02:50 Now I'm going to drag the label over, and let's just reposition this a little bit.
  • 02:57 And you can see currently, the field is Unbound and
  • 03:01 the text box just contains the very generic label of Text9.
  • 03:05 So let's deal with the text box first of all, let's change Label10.
  • 03:09 So let's call this EndDateLabel and the caption is just going to be End Date.
  • 03:16 We need to give this a little bit more breathing room so
  • 03:20 that we can see, and let's move this field over a little bit.
  • 03:24 There we go. I think that is good enough.
  • 03:26 I'm also going to do the same for the actual field.
  • 03:29 We're going to label this, so this is going to be, EndDate,
  • 03:35 and we have EndDateLabel.
  • 03:37 So now, if we take a look in the Control Source,
  • 03:39 currently we don't have anything in there.
  • 03:42 So if we click on the three dots right on the end,
  • 03:45 that's going to jump us into our Expression Builder.
  • 03:48 Because this field is going to be populated with a calculation,
  • 03:53 it's going to be DateLogged plus the SLA.
  • 03:55 Now if you take a look at my Expression Elements, you can see the current form is
  • 04:00 at the top frmRelatedTickets, and then we have all of the fields that I can use.
  • 04:05 Again, notice in here, I really do need to go through and
  • 04:08 rename some of these labels.
  • 04:10 So I'm quickly going to go away and do that so this is easier to read, and
  • 04:14 I'll join you back here in a couple of moments.
  • 04:17 So now that's looking a lot better.
  • 04:19 Now, I can see all of the fields currently in use in this end column underneath
  • 04:24 Expression Values.
  • 04:26 So I can use these fields in order to build my expression.
  • 04:30 So what do I want this to calculate?
  • 04:31 Well, we want to do the DateLogged,
  • 04:34 double click to put it into the area at the top, plus SLA.
  • 04:39 That is it.
  • 04:40 This is a very straightforward calculation.
  • 04:43 Let's click on OK.
  • 04:45 Now, take a look at the Control Source instead of selecting a table or a form,
  • 04:50 we have a calculation.
  • 04:52 Let's take a look at what that looks like.
  • 04:54 Let's right click and go to Form View.
  • 04:57 Now notice the End Date is a different format to the Date Logged.
  • 05:01 So I'm going to go back into Design View, and
  • 05:03 we're just going to change the format so it matches.
  • 05:07 So we're going to go for Median Date.
  • 05:09 Let's view the form again and check out what we're getting.
  • 05:13 So the first one here was logged on the 9th of April, and
  • 05:17 it has an SLA of seven days, so the end date is the 16th of April.
  • 05:23 The next one was logged on the 5th of August, it has an SLA of seven days,
  • 05:27 so the end date is the 12th of August.
  • 05:30 So my calculation is working correctly.
  • 05:33 Let's close down RelatedTickets.
  • 05:35 I'm going to say Yes to save the changes and reopen up TicketMaintenance.
  • 05:40 Now, if I scroll across, you can see that I have the End Date in there.
  • 05:44 And if I circle through some of these records,
  • 05:48 I have an end date for all of these related tickets.

Lesson notes are only available for subscribers.

Gift this course