One of our regular readers from South Africa, Mr. Nick Els, has made a suggestion to bring out an Article on usages of List Boxes with Date related settings. This is the First Part of a two part Series on this particular Topic. You may go through the earlier Posts: Selected List Box Items and Dynamic Query and Create List from Another List Box.

Descriptive names of Months or its Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in a variety of ways for comparing with Date Field values in Queries for filtering data. I mentioned variety because all methods require creating expressions at Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are being compared. We will split this Article into two parts instead of overcrowding with all of them here.

List Boxes can be created on Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports or on Report Parameter Forms for using in Queries for Data Processing tasks and so on. One or more Values from List Boxes can be selected and used directly in 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 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 get confused.

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 Orders table and this may cause errors while opening the Query created on 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.

 

4. De-select the Control Wizard (top right-side control on the Toolbox) if it is in selected state. Select the List Box Tool and draw a List Box as shown on 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.

 

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.

 

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]));
    

 

16. Open the Form LISTBOXDATE in normal View.

     

    
    

     

17. By default January month is selected in the List Box and Year 1997 is set in the Year Combo Box as 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 month and the Combo box value combined.

     

    The two Queries, which we have created, uses 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 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 Year 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 try to link the essential Library Files to your Project. Visit the Page Command Button Animation for details of Library Files and follow procedures explained there.

     

20. Open the first Query DISPLAY0_LISTBOX in 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 to extract 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 is 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 Text Boxes 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.