Multiple Criteria in Code

[AnnMV8]

Hi,

I'm hoping someone can help me. I'm not a programmer but found this really nice piece of code that I have been using but it needs to be altered. I had been getting help from Microsoft's website but then they stopped.

I am using Access 2002. I have the following code on a button which was altered by someone who had been helping me but it still doesn't work. The code allows me to pick multiple Course Titles from a list box and generate reports for only those I've chosen. Now I need to add a second piece of criteria to the list because I now have txtCourseTitle and dtmStartDate. Originally I only had txtCourseTitle. I need the date too because the same course can be listed more than once. The person that had been helping me told me I should be using IN instead of OR.

There were problems that were fixed but others popped up. Right now this line in the code is red.

"[dtmStartDate] = " & Format(YourDate Field, "\#yyyy\-mm\-dd\#")

Here is the code he gave me.

Private Sub Command8_Click( )

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummar yOfEvaluationsB yCourseParamete r
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelect ed.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.ItemsSelect ed
temp = Chr(39) & ctl.ItemData(va r) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriter ia, Len(strCriteria ) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDate Field, "\#yyyy\-mm\-dd\#")

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenRepor t "rptSummaryOfEv alutionsByCours e", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

[MikeTheBike]

Hi

On the basis that this works

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ")"

Then I would suggest this

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND [dtmStartDate] = #" & Format(YourDateField, "mm/dd/yy") & "#"

??

MTB

[AnnMV8]

Thank you for your help. I still had problems with this but did manage to get the it to work a different way. I really don't understand IN and it's a hard thing to Google. I went back to the original way. I'm not sure if it's the best way but it does work. The code is below:
Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String
'Dim dtmStartDate As Date
'Dim txtCourseTitle As String

Set frm = Forms!frmSummar yOfEvaluationsB yCourseParamete r
Set ctl = frm!lstCourses


'If no selection, display warning and exit
If ctl.ItemsSelect ed.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.ItemsSelect ed
temp = "[txtCourseTitle] = " & Chr(39) & _
Me.lstCourses.C olumn(0, var) & Chr(39) & " And " & "[dtmStartDate] = " & _
Format(Me.lstCo urses.Column(1, var), "\#mm\/dd\/yyyy#\" & " Or ")
strCriteria = strCriteria & temp
Next var
End If

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

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenRepor t "rptSummaryOfEv alutionsByCours e", acViewPreview, , strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub