A
Ann
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 am using Access 2002. I have code on a button click event. This code
allows me to pick multiple course titles, [txtCourseTitle] from a list box
and generate reports for all the courses I've chosen. Now I need to add a
second piece of criteria. I now have a list box with two fields,
[txtCourseTitle] and [dtmStartDate]. The [dtmStartDate] is entered as
01/01/2010 but is formatted as a long date. I need the date too because the
same course can be listed more than once in the listbox. I didn't write the
code below and it's beyond what I know, so I'm not sure how to change it so
it will pass both the txtCourseTitle and dtmStartDate fields to the query.
Can anyone help me out? Thanks in advance.
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 = "[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)
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria
ExitOpen:
Exit Sub
ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If
Set ctl = Nothing
Set frm = Nothing
End Sub
nice piece of code that I have been using but it needs to be altered.
I am using Access 2002. I have code on a button click event. This code
allows me to pick multiple course titles, [txtCourseTitle] from a list box
and generate reports for all the courses I've chosen. Now I need to add a
second piece of criteria. I now have a list box with two fields,
[txtCourseTitle] and [dtmStartDate]. The [dtmStartDate] is entered as
01/01/2010 but is formatted as a long date. I need the date too because the
same course can be listed more than once in the listbox. I didn't write the
code below and it's beyond what I know, so I'm not sure how to change it so
it will pass both the txtCourseTitle and dtmStartDate fields to the query.
Can anyone help me out? Thanks in advance.
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 = "[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)
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria
ExitOpen:
Exit Sub
ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If
Set ctl = Nothing
Set frm = Nothing
End Sub