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(YourDateField, "\#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!frmSummaryOfEvaluationsByCourseParameter
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 = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If
'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", 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