List Box and Date Part Two

Continued from LIST BOX AND DATE PART ONE.

Continued from LIST BOX AND DATE PART ONE

When working with date values in List Boxes, we’ve seen that it’s often necessary to convert the selected values into a compatible format before using them in data processing tasks. In this section, we’ll explore two additional examples that use different date expressions. Although the resulting output remains the same, the methods of specifying the date parameters will vary.

Typically, we can filter data using a date range — by entering a start date and an end date in text boxes on a report parameter form, or by entering these values directly into parameter queries to extract records from a source table or query. However, in the examples that follow, we’ll approach this task differently to gain a deeper understanding of how to work with date-related expressions in Microsoft Access VBA.

Modifying the Form.

1. Open the Form LISTBOXDATE that we have created in the earlier example in Design View.

2. Make a copy of the List Box and paste it into the same area of the Form. Drag and place it on the right side of the Combo Box. See the sample image given below:

   

3. Place the child Label on the top and display its Property Sheet (View - -> Properties). Change its Caption Property to List (Type-2).

4. Click on the List Box and display the Property Sheet (if you have closed it) and change the following Properties:

   • Name = List2
   • Row Source Type = Value List
   • Row Source = 01;"Jan";02;"Feb";03;"Mar";04;"Apr";05;"May";06;"Jun";07;"Jul";08;"Aug";09;"Sep";10;"Oct";11;"Nov";12;"Dec"
   • Column Count = 2
   • Column Heads = No
   • Column Width = 0";1.5"
   • Bound Column = 1
   • Default Value = 1
   • Multi Select = None

   At this point, let’s focus on a few important property settings of the List Box.
   First, check the Row Source property. In this example, the List Box items are entered as value pairs, such as 01; "Jan" for January, and so on for the other months. The Column Count property is set to 2, meaning the List Box contains two columns — the numeric value (e.g., 01) in the first column and the corresponding month abbreviation (e.g., Jan) in the second.

   However, when the List Box is displayed, only the second column (Jan, Feb, etc.) appears. This is because of the Column Widths property, which is set to 0";1.5". The first column has a width of  0 inches, effectively hiding it from view.

   Even though it’s hidden, the Bound Column property is set to 1, which means that when a user selects a month, the value from the first (hidden) column — such as 01 — is actually the one returned by the List Box.

   Finally, the Default Value property is set to 1, ensuring that if the user doesn’t make a selection, “Jan” (the first item in the list) is chosen automatically.

5. Create two TextBoxes to the left of the List Box and below the other TextBoxes. Change the Caption of the child Labels to Method-2 and Method-3.

6. Change the Property Values of the first Text Box that you have drawn now to the following Values:

   • Name = Method2
   • Control Source = =Format(DateSerial([cboyear],[List2],1),"mmm-yyyy")

7. Change the Property Values of the second Text Box to the following Values:

   • Name = Method3
   • Control Source = =[cboYear]*100+[List2]

8. Create a Command Button to the right of the existing two buttons and change the Property Values as shown below:

   • Name = cmdDisplay2
   • Caption = Display-2

9. Create another Command Button and place it to the right, and change the Property Values as given below:

   • Name = cmdDisplay3
   • Caption = Display-3

10. Display the VBA Code Module of the Form (View -> Code), and add the following VBA Code into the Module by copying and pasting it below the existing Code:

    Private Sub cmdDisplay2_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display2_listbox", acViewNormal
    End Sub
    
    Private Sub cmdDispaly3_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display3_listbox", acViewNormal
    End Sub
    

11. Open a New Query in Design View without selecting any file from the displayed list. Open the SQL editing Window (View -> SQL View), copy and paste the following SQL String, and save the Query with the name DISPLAY2_LISTBOX:

    SELECT Orders.*
    FROM Orders
    WHERE (((Format([orderdate],"mmm-yyyy"))=[Forms]![LISTBOXDATE]![Method2]));
    

12. Open another New Query in Design View, Copy and Paste the following SQL String into the SQL editing window, and save it with the name DISPLAY3_LISTBOX:

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

    Test Runs

13. Open the LISTBOXDATE Form in normal view and click on the Command Button Display-2. The Query DISPLAY2_LISTBOX will open up with filtered output data using the current value in the Text Box with the name Method2. Select different Values in the Year Combo Box and the new List Box and try it again. Check the accuracy of the data filtered.

    

    NB: If the Query displays an 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 the procedures explained there. The Orders Table doesn't have all twelve months of data except for the Year 1997. Check for the Range of months available in 1996 and 1998 years' data in this Table and select a month for available data for testing.

    Every time you select different Values in the List Box and the Combo Box, the value in the Text Box with the names Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the Command Button with a different selection of values.

14. Click on the Command Button Display-3 to open the Query DISPLAY3_LISTBOX with the filtered output using the Text Box named Method3 Value.

NB: You must change the Visible Property of these TextBoxes to No to keep them hidden from your Application Users. Study the expressions written in the TextBoxes and their corresponding Formula written in the Query Column to compare both values.

The List Box Settings

We used the Multi-Select Property of the List Boxes in the first two articles, Selected List Box Items and Dynamic Query, and Create List from Another List Box With Simple and Extended value settings,  but here we turned it off.

When you open the Form containing the List Boxes (with their Multi-Select property set to either Simple or Extended) for the first time, the Text Boxes that depend on expressions using values from these List Boxes will automatically display results based on the Default Value property, if one is defined. However, once you start interacting with the List Box—by clicking on an item—the Text Boxes may display #Error or become blank.

When the Multi-Select property is set to Simple, you can select or deselect items by clicking them one at a time.
When it is set to Extended, you can:

• Select a range of adjacent items by clicking the first and last items while holding down the SHIFT key, or by clicking and dragging the mouse across the list (without using SHIFT).

• Select non-adjacent items by holding the CTRL key and clicking on each desired item individually — just as you would in Simple mode.

We used the Multi-Select Property value equal to None (default setting) because our examples presented here work on a single item from the List Box.

• Double Action Command Button
• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users
• Crosstab Union Queries for Charts