List Box and Date Part Two ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from LIST BOX AND DATE PART ONE.

Continued from LIST BOX AND DATE PART ONE

We have seen that when working with date values in List Boxes we have to convert the selected value into one form or the other before they can be used for data processing tasks. We will try two more examples here with different date expressions. In all these cases the output is the same, but data selection parameters are used differently.

We can Filter Data using Data Range parameters, i.e., by entering Start-Date in a TextBox and End-Date in another, on a Report Parameter Screen, or by entering these values directly into Parameter Queries to filter data from the Source Table or Other Queries. But here we are trying to do it differently and get some familiarity in working with Date related expressions.

Modifying the Form.

Open the Form LISTBOXDATE that we have created in the earlier example in Design View.
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:
Place the child Label on the top and display its Property Sheet (View - -> Properties). Change its Caption Property to List (Type-2).
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 stage, your attention is drawn to a few property settings on this List Box. Check the Row Source Property setting. Here the List Box item values are inserted in pairs like 01; "Jan" for January and others. The Column Count property says there are 2 columns in this List Box. When the contents of the List Box are displayed, these values should appear in two columns, value 01 in the first column and Jan in the second. But here the value Jan from the 2nd column only shows up.
The first column value is kept hidden with the Column Width Property settings 0";1.5". The first column width of 0" prevents the value from showing up in the List Box. The Bound Column = 1 property setting takes the selected value from the first column, even if it is kept hidden. The Default Value = 1 says to take Jan as the default value if nothing is selected by the User.
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.
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")
Change the Property Values of the second Text Box to the following Values:
- Name = Method3
- Control Source = =[cboYear]*100+[List2]
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
Create another Command Button and place it to the right and change the Property Values as given below:
- Name = cmdDisplay3
- Caption = Display-3

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

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]));
```
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
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 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 the procedures explained there. The Orders Table doesn't have all twelve months' 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 name Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the Command Button with a different selection of values.
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 set 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 have 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 respectively but here we have turned it off.

When you open the Form with the List Boxes with these settings (Extended or Simple) for the first time, the TextBoxes with the expressions using values from the List Boxes will be loaded with the values calculated from the default values, if the Default Value Property is set to some value. But when you click on an item in the List Box the TextBoxes will show either #Error or will be Empty.

When the Multi-Select Property is set to the Simple Value you can click on List items one by one to select one or more items or to deselect them when clicked on it again. When the Extended value is set, then you can select a series of items, adjacent to each other, by clicking the first and last item by holding the SHIFT Key. Or click and drag the Mouse over the list of items without the use of the SHIFT Key. You must hold CTRL Key and click on items one by one for random selections as we did with the Simple value setting.

We have 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.