Multiple Criteria Listbox

[AnnMV888]

I've been searching everywhere on how to do this. I'm beginning to think it can only be done for one column list boxes and not two or more. I'm not a programmer so I'm really at a loss. Any help that can be offered would be greatly appreciated.

I have a form with a listbox, lstCourses, that has two columns. One is txtCourseTitle and the other is dtmStartDate. Both are needed to find the correct course. The dtmStartDate is entered as a short date and formatted as a long date). It appears in the listbox as a long date but if I enter a value in my query 04/22/2009 will find my data for that day.

I have code that I've listed below that will allow me to choose as many single column items from a listbox as I want and generate a report with all the items I've picked. I now want to do the same with the two column listbox.

Example: If I have three courses, Course One on 04/22/2009; Course Two on 05/01/2009 and Course Three on 01/02/2010. It would read like this: "Course One" And #04/22/2009# or "Course Two" And #05/01/2009# or "Course Three" and #01/02/2010#. How do I alter the code below so it looks for criteria from both Column One and Column Two together before it adds the next selection.

Private Sub cmdOpenEmployee_Click()
Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmCoursesParameter
Set ctl = frm!lstCourses
'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a Course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'opens report, closes form
DoCmd.OpenReport "rptCourses", acViewPreview, , strCriteria

Set ctl = Nothing
Set frm = Nothing

End Sub

 

[pbaldy]

I'm not clear on what you're trying to achieve, because to look at the date column you'd also need to be referring to a date field, would you not? In any case, the way to look at the second column is:

ctl.Column(1, var)

 

[AnnMV888]

I have a date field, it's called dtmStartDate.

The first row of the listbox shows Course One (column one) 04/22/2009 (column two). The second row of the listbox shows Course Two (column one) 05/01/2009. The third row of the listbox shows Course Three (column one) 01/02/2010 (column two) and I select the first and third row I am trying to put together criteria for the query that reads "Course One" And #04/22/2009# Or "Course Three" And #01/02/2010#. That would then produce two reports. One for Course One and one for Course Three. Every piece of code I find does this for a single column listbox, not a two column listbox.

If that is how I identify the second column, how do I identify the first column?

 

[pbaldy]

The column property is zero based, so 0 is the first column.

 

[AnnMV888]

Thank you for the help, using the columns gave me both parameters for my query for as many rows that I chose from the listbox.