List Box and Date Part One

Introduction.

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 List Boxes with date-related settings. This marks the beginning of a two-part series dedicated to exploring this specific topic. Please review our previous posts on Selected List Box Items and Dynamic Query, and create a List from Another List Box for a comprehensive understanding leading up to this series.

Descriptive names of the Months or their numerical form combined with Year Values from Combo Boxes or List Boxes can be used in various ways to compare with Date Field values in Queries for filtering data. I have mentioned the term variety because all methods require creating expressions at the Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are compared. We will split this article into two parts instead of overcrowding it with all of them 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. One or more Values from List Boxes can be selected and used directly with queries or VBA Routines to filter data from underlying tables, as we did in the earlier examples with List Boxes.

A reference to the selected List Box item can be set directly in a Query Criteria Row or extracted the selected value into a Text box with the help of a formula (like =[List1] in the Control Source property) and referenced 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. De-select the Control Wizard (top right-side control on the Toolbox) if it is in the selected state. Select the List Box Tool and draw a List Box 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 Combo Box Tool and draw a Combo Box at the top and to the right of the List Box. 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 Combo Box:
   • 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 Text Box below the Combo Box and change its Child Label Caption to Method-1. Select the Text Box and change the following Properties:
   • Name = Method1
   • Control Source = =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]),"yyyymm")

   The Visible Property of this Control can be set to No to hide it from Users if needed.

10. Create two Command Buttons below the List Box.
11. Change the first Command Button's Name Property to cmdDisplay0 and change the Caption Property to Display-0.
12. Create a second Command Button 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 be setting references to the List Box, Combo Box, and Text Box 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.

    

17. By default, January is selected in the List Box, and the Year 1997 is set in the Year Combo Box as the default value.

    The Text Box below the Combo Box displays the result of the formula that we have written using the List Box's current selection of the month and the Combo Box value combined.

    The two Queries that we have created use different methods to reference the contents of the List Box and the Combo Box.

18. Select a month from the List Box. Select a different Year in the Combo Box, 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 Command Button 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 List box and the Combo box, respectively. Close the Datasheet View of the Query before trying it out with the 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 the design view and check the criteria settings that we have created to compare the Order Date with the settings on the Form.

    We have created 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, we have set a direct reference to the selected month in the List Box, and the second column criteria are set with a reference to the current value of the Combo Box.

21. Click the Command Button with the Caption Display-1.

It will open the second Query DISPLAY1_LISTBOX 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 on the Queries and on the Form controls and try to understand how they work. You may create TextBoxes and Queries of your own using the same List Box and Combo Box values and try them out differently, which will give you 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