List Box and Date Part One

Date Selection ListBox.

One of our dedicated readers, Mr. Nick Els from South Africa, has proposed an insightful idea for an Article focusing on the practical applications of ListBoxes with date-related settings. This Topic is split into a two-part series to explore the topic's different aspects. Please review our previous posts on Selected ListBox  Items and Dynamic Query, and Create a List from Another ListBox for a comprehensive understanding leading up to this series.

Descriptive Month names or their numerical form combined with Year values from ComboBoxes or ListBoxes can be used in various ways to compare with Date Field values in Queries for filtering data. I used the term variety because all methods require creating expressions at the Query level or in a  TextBox on a Form to reformat the values into a compatible type before using them as criteria. We will split this article into two parts instead of overcrowding it here.

List Boxes can be created in Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports, or on Report Parameter Forms for use in Queries for Data Processing tasks, and so on. We select one or more Values from List Boxes and use them with queries or VBA Routines to filter data from underlying tables, as we did in the earlier examples with ListBoxes.

A reference to the selected List Box item can be set directly in a Query Criteria Row or extract the selected value into a TextBox with the help of a formula (like =[List1] in the Control Source property) and reference it in the Query to Filter Values from the underlying Table.

There are a few important aspects to understand about the Multi-Select property settings of List Boxes,  their advantages, limitations, and how they affect expressions that reference selected items in Text Boxes or Query Criteria. These topics will be explored in greater detail in the second part of this article, rather than delving too deeply into them here and risking unnecessary complexity at this stage.

Get Northwind Sample Tables.

1. Download the following four tables from the Northwind.mdb sample database. For now, we will be using only the Orders table. However, since the Orders table contains references to other tables, missing those related tables in your project may cause errors when opening queries based on the Orders table.

   If you’re unsure where the Northwind.mdb sample database is located, refer to the page “Saving Data on Forms Not in a Table” for location details and reference instructions.

   If you prefer to use a Table from your own project, you may do so, but you have to edit the expressions to change the Table Name and Field Names presented here before they are used with your Table.

   • Orders
   • Customers
   • Employees
   • Shippers

2. Copy and paste the following SQL String into the SQL Editing window of a new Query and save the Query with the name OrderYearQ.

   SELECT Year([OrderDate]) AS OrderYr
   FROM Orders
   GROUP BY Year([OrderDate]);
   

3. Open a New Form in Design View. If the Toolbox is not visible, then select Toolbox from the View Menu.
   

   Designing a Form with a List Box.

4. Deselect the Control Wizard (top-right control on the Toolbox), if it is in selected state. Select the ListBox Tool and draw a ListBox as shown in the design below.

   

   Right-click to open Large Image in New Tab/Window.

5. Click on the Child Label attached to the List Box and display the Property Sheet (View -> Properties), change the Caption Property to List1 (Type-1), and position the Label above the List Box.

6. Select the List Box, display the Property Sheet (if you have already closed it), and change the following property values as indicated against each one:

   • Name = List1
   • Row Source Type = Value List
   • Row Source = "January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"
   • Column Count = 1
   • Column Widths = 1.5"
   • Bound Column = 1
   • Default Value = "January"
   • Multi Select = None

7. Turn on the Control Wizard that we have disabled in Step 5. Select the ComboBox Tool and draw a ComboBox at the top and to the right of the ListBox. Select the OrderYearQ Query that we have created in Step 1 from the Queries List.

8. Change the following Property Values of the Child Label and the ComboBox:
   • Child Label: Caption = Year
   • Combo Box: Name = cboYear
   • Column Count = 1
   • Column Heads = No
   • Column Widths = 0.5"
   • Bound Column = 1
   • List widths = 0.5"
   • Default Value = =DMin("orderyr","orderyearQ")+1
9. Create a TextBox below the ComboBox and change its Child Label Caption to Method-1. Select the TextBox and change the following Properties:
   • Name = Method1
   • Control Source = =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]),"yyyymm")

   The Visible Property of this control is set to No to hide it, if needed.

10. Create two CommandButtons below the List Box.
11. Change the first CommandButton's Name Property to cmdDisplay0 and change the Caption Property to Display-0.
12. Create a second CommandButton to the right of the earlier one and change the Name Property to cmdDisplay1 and the Caption Property to Display-1.
    
    

    The Form Class Module Code.

13. Display the VBA Module of the Form (View -> Code), copy and paste the following VBA Code into the Module, and save the Form with the name LISTBOXDATE.

    Private Sub cmdDisplay0_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display0_listbox", acViewNormal
    End Sub
    
    Private Sub cmdDisplay1_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display1_listbox", acViewNormal
    End Sub
    

    Note: You must save the Form with the above name for our examples. We will set references to the ListBox, ComboBox, and TextBox on this Form to use their current values in Query Criteria Rows.

    Sample Queries.

14. Open a new Query in Design View without selecting any of the Files displayed. Display the SQL Window (View -> SQL View), copy and paste the SQL string given below, and save it with the name DISPLAY0_LISTBOX.

    SELECT Orders.*,
     Format([orderdate],"mmmm") AS MTH,
     Year([ORDERDATE]) AS XYEAR
    FROM Orders
    WHERE (((Format([orderdate],"mmmm"))=[Forms]![LISTBOXDATE]![List1]) AND ((Year([ORDERDATE]))=[Forms]![LISTBOXDATE]![cboYear]));
    

15. Create another Query with the following SQL string and save it with the name DISPLAY1_LISTBOX.

    SELECT Orders.*
    FROM Orders
    WHERE (((Format([OrderDate],"yyyymm"))=[Forms]![LISTBOXDATE]![Method1]));
    

    Test Runs.

16. Open the Form LISTBOXDATE in Normal View.

    

    Right-click to open the Large Image in a New Window.

17. By default, January is selected in the ListBox, and 1997 is set in the Year ComboBox as the default selection value.

    The TextBox below the ComboBox displays the result of the formula that is written using the ListBox's current month selection and combined with the ComboBox value.

    The two Queries were created to experiment with different methods and to reference the ListBox and ComboBox contents.

18. Select a month from the ListBox. Select a different Year in the ComboBox, if needed (but be careful with the month selection because not all twelve months' data are available except for the year 1997 in the Orders Table).
19. Click on the CommandButton with the Caption Display-0. The DISPLAY0_LISTBOX Query will open in Datasheet view with the data corresponding to the Month and Years settings in the ListBox and the ComboBox, respectively. Close the Datasheet View of the Query before trying it with different settings.

    NB: If the Query displays an error, then link the essential Library Files to your Project. Visit the Page Command Button Animation for details of Library Files and follow the procedures explained there.

20. Open the first Query DISPLAY0_LISTBOX in Design view and check the criteria settings to compare the Order Date with the settings on the Form.

    Create two columns with expressions for converting the Order Date Value into the descriptive name of the Month in the first column, and extracting the Year Value in the second Column. On the criteria row, set a direct reference to the selected month in the ListBox, and the second column criteria are set with a reference to the current value of the ComboBox.

21. Click the CommandButton with the Caption Display-1.

The second Query DISPLAY1_LISTBOX will open with the same result. But this query has only one column with an expression to compare the value with the Text Box contents on the Form. The Text box has the formula =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]), "yyyymm") to combine both Month and Year values, from the List Box and the Combo box respectively, together and referenced from the Query criteria row.

Study the expressions written in the Queries and form controls, and try to understand how they work. You may create TextBoxes and Queries using the same ListBox and ComboBox values and try them out to gain more insight into these methods.

• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users
• Crosstab Union Queries for Charts
• Union Query