List Box and Date Part One

Introduction.

One of our dedicated readers, Mr. Nick Els of 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. We encourage you to review our previous posts on Selected List Box Items and Dynamic Query and Create List from Another List Box for a comprehensive understanding leading up to this series.

Descriptive names of the Months or their Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in various ways for comparing 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 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 extract the selected value into a Text box with the help of a formula (like =[List1] in the Control Source property) and reference it in Query to Filter Values from the underlying Table.

There are a few things that we should know about the Multi-Select Property settings of List Boxes, advantages or disadvantages, and how they influence the expressions, which we build using the selected item in Text Boxes or in referencing directly on Query Criteria. These can be looked into in the second part of this article instead of getting too deep into them at this stage and needing clarification.

Get Northwind Sample Tables.

1. Download the following four Tables from the Northwind.mdb sample database. We will be using only the Orders table now. There are references to the other Tables from the Orders table and this may cause errors while opening the Query created on the Orders Table without the other tables in your Project. If you are not sure about the Location of the NorthWind.mdb sample Database, visit the Page Saving Data on Forms not in Table for its location references.

   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 View Menu.
   

   Designing a Form with 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.

   

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 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 month 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 all twelve months' data are not 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 some 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 List Box and 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