Filtering Report Results Using the Filter Property

In this tip we are going to filter the results of a Report to display records with order amounts above a given value.  In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button.  The user enters an amount value in the text box, and clicks the command button.  The command button then runs a small amount of VBA code to open the report.  As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders).  In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).  

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post).  I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user.  To add this code:

1. Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).  
2. When the PROPERTY SHEET opens, click the EVENT TAB.  
3. Then select the ON CLICK event by clicking into that particular cell of the grid.  
4. A three dot  symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.  
5. Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor.  The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

---

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

    DoCmd.OpenReport "rptOrdersFiltered", acViewReport

    DoCmd.Close acForm, Me.Name

Leave:

    Exit Sub

    

MyError:

    MsgBox "Error " &  Err.Number &  ": " &  Error$

    Resume Leave

End Sub

---

The two important lines in this section of code begin with DoCmd (meaning do command).  The first of these is the command to open the form in Report View.  The second closes the Dialog form once the report has opened.  NB it is very important the report opens before  the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.  

The Report

The quickest way to create the Report is to base it on our table.

1. Select tblOrders in the Navigation Pane.  It will highlight in orange.
2. Then select  the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP). 

This creates a basic report which we can then then modify in DESIGN VIEW as required. 

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

1. Click the Square Box in the top right hand corner of the Report Design Grid.  This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
2. Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
3. Click the DATA TAB of the pane.
4. Make sure the SELECTION TYPE is set to REPORT.  This should be the case if you carried out step 1 successfully.  Otherwise just change the selection to Form in the drop down list.
5. Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] .  This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
6. Then set the FILTER ON LOAD PROPERTY to YES.  This ensures the filter is applied when the forms loads.
7. Save the Report as rptOrdersFiltered.
8. Close the Report

Figure 5: The Form's Filter Properties.  Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run.  Just click the frmParameter form in the Navigation Pane to begin.  The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter: 

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.

Creating a Dialog Form

Dialog Forms are an integral part of most customized Access Database Applications.  As developers, we set them up to enable our applications to communicate and interact with users.  The simplest sort of dialog box is one which displays a message.  These may be created using VBA's MsgBox Function or the MsgBox Macro Action.   Others involve a greater degree of customization, especially when the application requires the user to provide information such as parameters for a query.  One way to do is to create a standard Access form and modify it's properties to make it into a Dialog Form.

Figure 1: This Dialog Form obtains date parameters used to restrict the
records displayed in a report.  You may remember this was NorAzri's chosen
method for filtering report results mentioned in my last post.

To create a Dialog Form such as that displayed in figure one, we begin by creating an unbound form.  This is done by clicking the FORM DESIGN icon from the FORMS group of the CREATE ribbon.  The term unbound means that the form is not bound to a particular Table as with conventional forms.  The information it is designed to collect is temporary, and does not need to be stored.

When a new form opens in design view, the screen will show a blank grid in the main window, and a variety of  form controls in the CONTROLS group of the DESIGN ribbon.  The Dialog Form in figure 1 uses four Combo Box Controls and a Command Button.  The values displayed in the Combo Boxes are defined in the Row Source Property.  I recently blogged about Customizing an Access Combo Box.  If you are interested, I recommend you check it out by following the link above (just remember that the combo boxes in this example do not need a Control Source because we do not need to store it's data in a table).

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form.  So how is this done?  The answer to this lies in a number of key form properties.  The two most important properties to understand are the POP UP and MODAL properties.

The POP UP property ensures that when the dialog form is opened, it appears as a pop up box rather than a full size form. Otherwise it would just appear as an ordinary form rather than dialog form.  As such this property should always be set to YES.  Setting the MODAL property to YES ensures the user cannot ignore the dialog form by clicking on a ribbon icon or form control, for example.  The idea here is that your application is waiting for a user response. Access will not do anything until that response is given, and/or the dialog form is closed.  These properties are located on the OTHER TAB of the PROPERTY SHEET.

Figure 2: Set the POP UP and MODAL properties to YES.  This is done
 from the OTHER TAB of the PROPERTY SHEET.  The property
sheet is accessed by clicking the PROPERTY SHEET icon
on the TOOLS group of the DESIGN ribbon.

NB When you change these properties make sure the SELECTION TYPE of the PROPERTY SHEET is set to FORM.  If it is not, just click the drop down list at the top of the sheet and change it. Alternatively click the square box at the top left hand corner of the DESIGN GRID. 

If you look again at the dialog form in figure1 you will notice there are no navigation buttons or record selectors which forms normally have by default.  To remove these just go to the FORMAT TAB of the PROPERTY SHEET and change the NAVIGATION BUTTONS and RECORD SELECTORS properties to NO.

You may also notice that our dialog form has a border style specific to dialog forms.  One of its features is that the user cannot resize it, thereby diminishing its impact!  This is attained by changing the BORDER STYLE property to DIALOG.

One of the more subtle properties that you might want to change is that of the SCROLL BARS property.  I recommend you set this to NEITHER.  Apart from the fact you should not need to scroll a dialog form, Access also leaves a thick white line at the bottom of the form if this is not changed.  This looks particularly bad if you choose a different Back Color for your form.

One last property alteration that I recommend, is the addition of a form Caption.  This appears in the top border of the dialog form.  In figure 1 I have set this to "Please Enter a Date Range".  The caption does not have to be a question, generally it is just a form title.  Either way you will need to choose your own caption and enter it in the CAPTION property, the first property on the FORMAT TAB.

As far as the actual function of the dialog form is concerned, this particular example runs a VBA sub when the command button is clicked.  The code creates an SQL WHERE statement based on information obtained from data entered by the user on the dialog form.  This is part of the code that I used:


varYearFrom = Me!cboYearFrom
varYearTo = Me!cboYearTo
varMonthFrom = Me!cboMonthFrom
varMonthTo = Me!cboMonthTo

strSQL = "(Year between " & varYearFrom & " AND " & varYearTo & ") AND (Month between " & varMonthFrom & " AND " & varMonthTo & ")"


As you can see, each combo box value is stored in a variable in the first four lines of code.  These variables are then integrated into the string containing the SQL Statement. Once we have the SQL Statement stored in the string strSQL, this can be passed as the WHERE parameter of the DoCmd.OpenReport Method like this:


DoCmd.OpenReport varForm, acViewReport, , strSQL


Then, when our report opens, only records falling within the criteria set in the SQL WHERE statement are displayed. In this case these are all the records between January and December 2011 (see figure 1 above).

Manipulating Dates Using the DatePart Function

I was recently asked a question on my Access 2007 Tutorial Facebook Page which involved the manipulation of dates.  In my answer to this question, I suggested a solution that made use of the DatePart Function.  If you are interested, I was replying to NorAzri's comment on his post of the 5th Sep 2011.  However, manipulating dates in this way is quite an interesting area, so I thought I would use this blog to write a bit more about the function.

So what does DatePart actually do?  Put simply, the DatePart Function allows us to isolate part of a given date.  In so doing we may then go on to group or retrieve records according to the date interval set . We use the function by passing an interval parameter telling Access which part of the date we are interested in, and a second parameter which is the date itself.  The syntax for the function is constructed as follows:

DatePart("Interval", DateValue)

The interval parameter is comprised of the same values as that used in the related DateDiff Function - a function I blogged about in August.  These intervals may pertain to the Year ("yyyy"), the Quarter ("q"), or the month ("m") to name but three. There are ten possible interval types in total (going right down to hours, minutes, and seconds).

However, NorAzri was interested in filtering his records by month.  So in order to do this we passed "m" for the interval parameter, and fldDate (a field value from the table) for the date parameter.  The syntax for this was written as follows:

DatePart("m", [fldDate])

The result of the function gave us a numeric month value based on the full date.  For example, if the date in fldDate had been 16/09/2011, the function would have returned the value 9. This is useful when we have a list of dates, such as that in Figure 1 below, which we want to group by month value in a query or report.

Figure 1:  List of Orders with dates.

So lets take a look at how we would use the DatePart Function in a query to group these order records by month:

Figure 2

As you can see in Figure 2 above, we have created a calculated field called TheMonth using the DatePart Function.  The interval, "m", has been passed in the first parameter, and the second parameter references the OrderDate field from tblOrders.  We have also clicked the TOTALS icon from the SHOW/HIDE group of the DESIGN ribbon, setting TheMonth and Total fields to GROUP BY and SUM respectively.

When we run the Query, any month containing an order will be represented in the results as its own individual row.  The first column then displays the Month value derived from the DatePart function in TheMonth, and the second contains the sum of order totals for the particular month in question.  Figure 3 below shows the query result for our sample data:

Figure 3: Results of Query with Calculated
Field using the DatePart Function.  SumOfTotal
gives us the total for each group of order records
falling within each Month.

Another excellent use of DatePart would be to filter a group of records using a criteria based on this function.  For example, if we wanted to show each individual record for all orders falling within the month of May, we would construct our Query as follows:

Figure 4

As you can see, we have kept our calculated field called TheMonth.  However, instead of grouping all the records from tblOrders by month, we have added the month value =5 as a criteria for this column.  When we run the query, all Orders from May will be displayed.

Figure 5: The result of the second Query filters
out all orders from May.

If we wanted, we could convert this to a parameter query, and use it as the data source for a report.  This was something I recommended to NorAzri who wanted to select the Month from a Combo Box on an unbound form, and then produce a report which would filter out records falling within the month in question.  If you are interested in seeing the solution I suggested, just follow the link at the top of this post and look down the stream.

How to Display a Form Automatically when your Application Opens

This is a quick tip on how to automatically display a form when the user opens your Access Application.  In addition to improving User Friendliness, your database design will also appear much more professional.  The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening.  This will give them a good start point from which to navigate your system.

Figure 1: Switchboard Form opens Automatically
when the Application Opens.

1. Open the Database and create a Switchboard Form similar to that in figure 1 above.  Call the form frmSwitchboard.
2. Click the MICROSOFT OFFICE button in the top right hand corner of the Access Screen.  
   
3. Click the ACCESS OPTIONS button at the bottom of the open pane.  This opens the ACCESS  OPTIONS dialogue form.
4. Select CURRENT DATABASE from the menu on the left side of the dialogue form.  This displays the Options for the Current Database (see figure 2 below).

   

   Figure 2: Options for the Current Database.

5. The fourth option down from the top is DISPLAY FORM.  Click the Combo Box arrow and select frmSwitchboard from its drop down list, 
6. Click OK to close the ACCESS OPTIONS form.
7. Close the database.

The next time you open your application, the Swichboard is displayed automatically without the user needing to select it from the Navigation Pane.

Removing Multiple Records with a Delete Query

Imagine you have a contact database containing a list of suppliers.  One of those suppliers proved themselves unreliable once to often, so your company decides not to use them any more.  It is your job to delete any names from this list that belong to this particular supplier. The only problem is, the contact database contains hundreds of names, a number of which are from the suppliers company. This is where an Access Delete Query is going to prove very useful.

Delete Queries are a type of Action Query ie a Query which performs some sort of action on our database.  In the case of a Delete Query, Access will find a group of records matching a given criteria and delete them from the database table.  As such, we can make good use of a Delete Query in the scenario outlined above.  By means of a Delete Query, we can find all the records of staff who work for the ex-supplier and delete them from the contact list.  Lets take a look at the contact list we are going to working with (shortened for the purpose of this exercise):

Figure 1: The Contact List upon which
we shall run our Delete Query.

Lets say the name of our ex-supplier is Company 5.

So how do we create a Delete Query?

It's quite simple really.  The first stage is to create an initial select query that would give us a list of all the names of staff working for Company 5.  This involves entering "Company 5" as the query criteria. Once we have run the query and checked its results, we just need to change it to a Delete Query and click RUN again.

How to Create a Delete Query

Stage 1 - Creating the initial Select Query

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group of the CREATE ribbon.
3. Select tblContacts from the SHOW TABLE dialogue box.
4. Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID.  This is a way of getting the query results to display all fields from the table without having to select each one individually.
5. Then Drag the Company field from tblContacts down to the second column of the grid.  We have added this field separately because we are going to enter a criteria in this column.
6. Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"

The Select Query has now been created.  It should look like this:

Figure 2: The Select Query created in the
first stage of the Delete Query.

It is advisable to run the query at this point and check the results are correct.  They should look like this:

Figure 2: The results from the Select Query.

As you can see, our select query has found four records from tblContacts matching the criteria of Company 5".  Since this is the correct result for the dataset we are working with,  we can move onto the second stage of the process: converting the Select Query to a Delete Query.

Stage 2 - Converting the Initial Select Query to a Delete Query

1. If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT QUERY icon is highlighted orange.  We need to change this to DELETE QUERY.  To do this just click the DELETE QUERY icon further along the group.  

   

   Figure 3: The QUERY TYPE group of the DESIGN ribbon.
   The DELETE QUERY icon is highlighted orange.
2. After the clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their place.  Access has filled in the values of FROM and WHERE in the first and second columns respectively.  These are SQL Keywords: the FROM keyword indicates the first column contains fields from tblContacts, and WHERE indicates the Company column contains a criteria against the data stored in this field. 

   

   Figure 4: The QUERY DESIGN GRID for our
   DELETE Query.  Notice the new row for DELETE
   containing the SQL FROM and WHERE
   Keywords.

3. Click RUN from the QUERY RESULTS group.  
4. Click YES when prompted whether we want to delete the number of rows matching our query criteria.  This will be four rows for the dataset we have been working with.

We can now go back and open the tblContacts table.  As you can see from Figure 5 below, all Company 5 contacts have been removed by our DELETE QUERY.

Figure 5: The tblContacts table after Company 5
contacts have been removed.

Using a Crosstab Query to Present Summary Data

In this post we shall use the power of an Access Crosstab Query to summarize and restructure our data into a clear and concise format.  We shall illustrate how to do this with sales made by employees of a Real Estate Company (who are referred to as Estate Agents in the UK).

So in what way does a Crosstab Query improve the presentation of summary data?  Well lets imagine our Real Estate Company asks us to summarize a list of sales made by each of its employees, for each quarter, over the period of a year.  This list has fields for Employee, Quarter and SaleValue.

Figure 1: This is the raw data that will form
the basis of our Crosstab Query.

As you can see in Figure 1 above, this raw data provides us with all the necessary information, but is difficult to digest in its present form.  What we are interested in, is the total SalesValue, for each Employee, for each Quarter.  With the help of a Crosstab Query, we can restructure this information so that each Employee Name becomes a single Row Heading on the left side of the table, and each Quarter Value is grouped together to form Column Heading's across the top of the table.  Since there are three employees, and four quarters in our data, this will give us a table with three rows and four columns.  The Sum of the Sales value then appears where each Employee Row intersects with each Quarter Column.  This is how our Crosstab Query will be structured:

	|Quarter 1|	Quarter 2|	Quarter 3|	Quarter 4|
Employee 1	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|
Employee 2	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|
Employee 3	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|

Creating a Crosstab Query

So how do we create a Crosstab Query?

Well, there are two main ways: the first with the Crosstab Query Wizard, and the second using the Query Design Grid.  We are going to focus on the second method which involves creating the Crosstab Query from Scratch.  However if you do wish to use the Crosstab Query Wizard, you can select the CREATE TAB, click the QUERY WIZARD ICON from the OTHER group, highlight CROSSTAB QUERY from the list in the dialogue box, and then click OK.  When the Wizard starts, follow the instructions to select the Table/Query, the field to be used as Row Headings (SalesPerson), the field to be used as Column Headings (Quarter), and the aggregate function to be used to summarize the SaleValue (Sum).

However, here is the method to create a Crosstab Query from Scratch using the Query Design Grid:

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group.
3. Select the table or query to be used from the SHOW TABLE dialogue box.  The one I have used is called qrySales.
4. Click the CROSSTAB icon.  This is located in the QUERY TYPE group of the DESIGN ribbon.  Notice how two new rows, Crosstab and Totals, appear in the query design grid.
5. Drag the three field names from qrySales down onto the grid.
6. Go to the Crosstab row of the  SalesPerson column on the QUERY DESIGN GRID. Then select Row Heading from the drop down box in that cell.
7. Next go to the Crosstab row of the Quarter column.  Then select Column Heading from the drop down list.
8. As you may recall, the sum of SaleValue is going to provide the summary data in our table.  To do this, go to the Totals row of the SaleValue column.  Then select Sum from the drop down list.  Then move down to the Crosstab row of the SaleValue column, and then select Value from the drop down list. 

The QUERY DESIGN GRID should  now look like this:

Figure 2: The QUERY DESIGN GRID for our Crosstab Query.

 When you run the Crosstab Query our results should appear like this:

Figure 3: Results of our Crosstab Sales Query.

Its worth pointing out that we can add another row heading containing a Total SalesValue for each SalesPerson across the four quarters.  To do this just go back to the grid and add an additional column for SalesValue (you might want to give the column the alias of Total).   Then select Row Heading from the Crosstab Row on the grid. When run, it should look like this:

Figure 4: Crosstab Query with additional Row Heading comprised of the row Total.

Handling the Conditional: Using the IIf Function

Let's imagine we have an Access table containing a list of academic exam results.  There is a field for Subject and a field for the percentage Result , but we do not have one telling us whether each percentage result is deemed a Pass or Fail.  So what is the best way to 'store' this missing information?

Figure 1: An Access Table containing
Subject and Result fields.

We might be tempted create such a Pass/Fail field, but this is generally considered bad database design practice: since it is possible to calculate whether the student passed or failed on the basis of the percentage result, we would be storing redundant data.  Creating a Query would be a much better option.  However, we need to display whether the result is a Pass or a Fail, so simply entering a criteria to filter out all results above or below a certain percentage is not going to do the job: this would only provide us with a list of Passes or a separate list of Fails.  We just want one column stating Pass or Fail.

A great way of doing this task would be to use the IIf function as a new calculated column within the Query.  This will enable us to display a value indicating whether the exam has been passed or failed.  The IIf function allows us to specify a conditional expression (in a similar way to a query criteria), but then to go on and specify a value to be displayed based on whether the result of the expression happens to be true or false.  In our case we want the expression to determine whether the value contained in a percentage result field is, say,  greater than 50%, and if it is, display "PASS", or else display "FAIL".

The IIf function is constructed as follows:

IIf(Conditional Expression,  True_Condition, False_Condition)

The first parameter we pass for this function is the conditional expression.  In our case this would be Result > 50.  The Second parameter we pass is the string value which appears if the condition is true (for us this would be "PASS"); and the third parameter is the string value which appears if the condition is false (for us this would be "FAIL").  As such we would construct our own particular IIf function like this:

IIf([Result] > 50,  "Pass", "Fail")

We shall enter the this expression into our query as follows:

Figure 2: The IIf Function has been entered into the third column using the alias Pass.

As you can see we have entered our IIf function in the last column on the right.  We have used the alias "Pass" to describe the data to be displayed in this column.  (For more information about alias's and using functions in Access Queries, please see this explanation in relation to the DateDiff Function).  Just remember to separate the alias name from the function with a colon as shown in Figure 2 above.

Now lets run the query and see what we get:

Figure 3: the results of our query to calculate whether
a student has passed or failed an exam.

As you can see, the IIf function has correctly assigned a Pass or Fail based on the percentage Result attained.

Calculating Date Difference: Using the DateDiff Function

Suppose you want to calculate the difference between two dates.  An example of this might be a Library Management System: somebody returns an overdue library book, and receives a fine based on the number of days it happens to be late.  Our two dates in this instance would be the DueDate and the actual DateReturned.  To calculate the difference between these two dates we may use the DateDiff Function.

We could use the DateDiff Function as a Calculated Control (just put an = sign in front of it and enter the function as the textbox's CONTROL SOURCE), or within a VBA Code Module.  In this example, however, we shall use it within an Access Query.

Let's take a look at how the DateDiff Function is constructed:

DateDiff("interval", FirstDate, SecondDate)

As you can see, the function passes three parameters: these are "Interval", FirstDate, and SecondDate.  The interval parameter allows us to specify whether we want the function to return the difference in, for example, days, weeks, or years.  In our case we are interested in how many days a book is overdue, so we enter "d" as a string value. (Had we wanted the interval in weeks, we would have entered the parameter as "ww"; or years as "yyyy".  There are also a number of other options available such as quarter: "q"; hours: "h"; and minutes: "n").  The FirstDate and SecondDate parameter's refer to the two dates between which the difference is to be calculated.  In our case, these dates are contained in the DueDate and ReturnDate fields of a table called jnkLoan. As such, we would construct the DateDiff Function as follows:

DateDiff("d", DueDate, ReturnDate)

Incidentally if the book was returned early, ie the DueDate is later than the ReturnDate, the function would return the Date Difference as a negative value.  As such, the order in which the two date parameters are entered will make a difference to the return value of the function. It is the same principle as the subtraction of a smaller number from a larger number, and vice versa. With the DateDiff Function, the value of the first date is "subtracted" from that of the second, to return the date difference which we defined in the interval parameter.

So lets take a look at how we may use this function in the context of an Access Query:

Figure 1: The DateDiff Function is used in the last column on the right.

For the sake of simplicity I have used a table with just three fields - LoanID, DueDate, and ReturnDate.  However, as you can see from the screen shot above, our query has four columns.  The first three columns contain the fields of our jnkLoan table, but the last column on the right is not bound to any field; it is actually a calculation based on the DateDiff Function that we constructed above. To use the function within this query we have just had to choose an alias for the column - ie a name we make up to refer to the column.  In this example, we have used an alias called Difference.  A colon is then used to separate the alias from our DateDiff Function.

Another thing worth mentioning is how our DateDiff Function refers to the DueDate and ReturnDate fields of the jnkLoan table.  As such our Query bases the DateDiff calculation on the date values contained in the relevant record (ie that which matches the criteria set in the LoanID column) of this table.  Here is the result:

Figure 2: Results of the Query using the DateDiff Function.
The result of the DateDiff calculation is displayed in
the last column on the right.

Our query criteria selected a record containing the LoanID 427648205.  The DueDate for this record was the 08/08/2011 and the ReturnDate is 12/08/2011.  Our DateDiff Function, displayed in the last column on the right, has calculated the book is being returned four days overdue.

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.

Object Dependencies

One thing that I find fascinating about Access Database Development is working with multiple database objects. Take an Access Form Object, for example.  Most forms have a Table or Query Object as a Record Source.  This is what is meant by the term Object Dependency: in this example, the Form Object is dependent on the Table or Query which supplies it's data. The same is true when a Query depends on a table or tables, or a form depends on another Form used as a Subform.  An Access Database is full of these  interrelated object dependencies which, in larger systems, can soon become quite complex.  This is where the Object Dependencies Pane come in quite useful.

The Object Dependency Pane works by the developer selecting or highlighting an object from the Navigation Pane, then clicking the OBJECT DEPENDENCIES icon located in the SHOW/HIDE group of the DATABASE TOOLS ribbon.  When the pane opens you have the option of displaying all Objects which are dependent on the selected Object, or all Objects upon which the selected Object Depends.   

Figure 1: The Object Dependencies Pane.

The screen shot above shows the dependencies for a form object called frmCustomer (which is a simple form displaying the details of a customer, with a subform displaying all orders the customer has made).  The pane shows all the Tables, Queries, Forms and Reports that frmCustomer is dependent upon.  As you can see, the form uses two tables - tblCustomer, and tblOrders.  It also uses another form frmOrders.  Although you cannot tell from the Object Dependencies Pane that tblCustomer is the Record Source for the main form and tblOrders for the subform, you do get a good idea that this is the case from the names allocated to each.  

If you want to open one of the Objects listed in the Object Dependencies Pane (in Design View), just click the Object name. It is also possible expand the list of Objects.  So, for example, if you were interested in finding out what tblCustomer depends on, just click the small + sign to the left of the object name and a new tree level opens out. As such, this tool can be used to trace some fairly complex object hierarchies where multiple levels exit.  

Dealing With a Combo Box Entry that is "Not In List"

Following on from my last post on Customizing an Access Combo Box, this tip is about dealing with a Combo Box Entry that is Not In List.  Basically this occurs when instead of selecting an item from the Combo Box list, the user manually types in an entry that is not one of the items from the drop down list. It works by running a small section of VBA code when the Combo Box NOT IN LIST Event fires (more about this later).  The code then creates a new record containing the user's entry in the table upon which the combo box list is based.

Let's use the example of a Combo Box on a Products Form to illustrate this.  The Combo Box is used in this example to enter the Category field of the Product record.  The Record Source of the form is tblProducts, and the Row Source of the Combo Box is tblCategory.  If you want to run a copy of this example, you can download the Not In List Example Database by clicking the link (you will need to Enable the Content if you save it a location that is not trusted).

Figure 1: The Products form with a Combo Box on the Category field.

As you can see from Figure 1 above, the Category field uses a Combo Box with a list of potential categories.  These are stored in a separate table called tblCategory, which is the value of the Combo Box ROW SOURCE property.  The screen shot shows that there are three categories - Office Equipment, Office Furniture, and Stationery.  So what happens if the user wants to create a new product record, for say, a software package? There is no existing category for Software, so once the user enters the product name - lets say it is MS Office Access 2010 - he or she is unable to select a suitable category from the drop down list.

Now might be good time to mention the Combo Box LIMIT TO LIST property (located in the DATA TAB of the PROPERTY SHEET).  When this is set to YES (which is the case in our example), Access would normally display an error message saying THE TEXT YOU ENTERED IS NOT AN ITEM IN THE LIST; it then asks the user to select an item which is, or type in text that matches one of the listed items. This message is show in Figure 2 below:

Figure 2: The default message shown when an item is not in list.

However, when LIMIT TO LIST is set to yes, Access first fires the NOT IN LIST event. This enables us to pre-empt the standard message by writing code to display a custom message of our own.  This code also gives the user the opportunity to add the new Category to the table which is the row source for the combo box list.

Let's take a look at the code used in our example database.

---

Private Sub ctlCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo myError
    
    Dim rst As DAO.Recordset
        
    Set rst = CurrentDb.OpenRecordset("tblCategory", dbOpenDynaset)
    
        If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
                & NewData & " as a new category?", _
                vbYesNoCancel + vbDefaultButton2, _
                "New Category") Then
                
            rst.AddNew
                rst!categoryName = NewData
            rst.Update
            
            Response = acDataErrAdded
            
        Else
        
            Response = acDataErrContinue
            
        End If
       
leave:

    If Not rst Is Nothing Then
        rst.Close: Set rst = Nothing
    End If
    
    Exit Sub
    
myError:
   
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave
    
End Sub

---

We enter this code into the Visual Basic Editor by clicking the three dot symbol on the far right of  ON NOT IN LIST (located on the EVENTS Tab of the Combo Box's PROPERTY SHEET).  The CODE BUILDER option was then selected from the CHOOSE BUILDER Dialogue Box.

Figure 3: The EVENTS Tab of the PROPERTY SHEET.

So how does the code work?

The first line of code was created automatically by Access when the VBA editor was opened via the ON NOT IN LIST line of the property sheet.  There are two arguments enclosed within the brackets - NewData and Response.  The first of these contains the new category value just entered by the user as a string variable.  The later relates to how access is going to handle the Not In List Event.  It's default value is 0 which represents the standard way of doing so ie displaying the error message and preventing the user from adding the new data.  Needless to say, we are going to alter this value later on in the code!

The third and fourth line of code relates to the object variable rst which represents a DAO recordset based on tblCategory, the Row Source of our Combo Box list.  Object variable rst is first declared in the Dim Statement, and then Set to tblCategory via the openrecordset method of the database object.

The fifth line of code relates to the custom message displayed to the user when he or she enters an item that is not in list.  The msgbox function displays our message as well as determining which buttons are offered, which button is the default, and the msgbox title.  The user response, ie the button the user clicks, is then processed by the If statement - so if the user clicks YES, the code branches to the code below where a new category record will be added.

This new category record is added using the addNew method of the Recordset Object.  The categoryName field is then set to the value of the NewData variable- ie the value of the new category entered by the user which was passed by Access as a parameter to the sub.  The new record is then saved to tblCategory using the Update method of the Recordset Object.

The next line of code changes the value held in the response variable (passed by Access as a parameter in the first line of the sub) via the acDataErrAdded constant.  Doing so tells access that new data can be added to the Combo Box list, and not to display the default Not In List error message.  It also Requeries the Combo Box list so that the new data appears immediately.

However, if the user had clicked the NO or CANCEL Command button mentioned above, the program flow branches to the Else Statement where it goes on to run the line of code changing the value held in the Response variable via the acDataErrContinue constant.  This tells Access not to add the new data to the Combo Box list, but continue without displaying the default Not In List message.

After the End If statement the program flow converges once again.  The last section of code (before the error handling section) deals with closing the rst Recordset Object and re-setting its value to nothing.  The sub's program flow then exits the sub via the Exit Sub statement.