Here is how I have placed the code you provided in the whole thing:
Code:
Option Compare Database
Option Explicit
Private Sub cmdPreviewReports_Click()
On Error GoTo cmdPreviewReports_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strInstructors As String
Dim strCourseType As String
Dim strCourseTypeCondition As String
Dim strRoleType As String
Dim strRoleTypeCondition As String
Dim strStartDate As String
Dim strEndDate As String
Dim strSql As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "q_Parameter_Form" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM q_jt_MCR_Instructor_Roles"
cat.Views.Append "q_Parameter_Form", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "q_Parameter_Form") = acObjStateOpen Then
DoCmd.Close acQuery, "q_Parameter_Form"
End If
' Build criteria string for Instructors
For Each varItem In Me.lst_Instructors.ItemsSelected
strInstructors = strInstructors & "," & Me.lst_Instructors.ItemData(varItem) & ""
Next varItem
If Len(strInstructors) = 0 Then
strInstructors = "Like '*'"
Else
strInstructors = Right(strInstructors, Len(strInstructors) - 1)
strInstructors = "IN(" & strInstructors & ")"
End If
' Build criteria string for CourseType
For Each varItem In Me.lst_Course_Type.ItemsSelected
strCourseType = strCourseType & "," & Me.lst_Course_Type.ItemData(varItem) & ""
Next varItem
If Len(strCourseType) = 0 Then
strCourseType = "Like '*'"
Else
strCourseType = Right(strCourseType, Len(strCourseType) - 1)
strCourseType = "IN(" & strCourseType & ")"
End If
' Get CourseType condition
If Me.optAndCourseType.Value = True Then
strCourseTypeCondition = " AND "
Else
strCourseTypeCondition = " OR "
End If
' Build criteria string for RoleType
For Each varItem In Me.lst_Role.ItemsSelected
strRoleType = strRoleType & "," & Me.lst_Role.ItemData(varItem) & ""
Next varItem
If Len(strRoleType) = 0 Then
strRoleType = "Like '*'"
Else
strRoleType = Right(strRoleType, Len(strRoleType) - 1)
strRoleType = "IN(" & strRoleType & ")"
End If
' Get RoleType condition
If Me.optAndRoleType.Value = True Then
strRoleTypeCondition = " AND "
Else
strRoleTypeCondition = " OR "
End If
'Build Criteria String for date range
Select Case True
case IsDate(Me.Start_Date) and IsDate(me.End_Date) Then
strSQL = strSQL & "Course_Date between #" & Me.Start_Date & "# AND "#" & Me.End_Date & "#"
case IsNull(Me.Start_Date) and IsDate(me.End_Date) Then
strSql = strSql & "Course_Date <= #" & Me.End_Date & "#"
case IsDate(Me.Start_Date) and IsNull(me.End_Date) Then
strSql = strSql & "Course_Date >= #" & Me.Start_Date & "#"
End Select
' Build SQL statement
strSql = "SELECT q_jt_MCR_Instructor_Roles.* FROM q_jt_MCR_Instructor_Roles " & _
"WHERE q_jt_MCR_Instructor_Roles.[InstructorID] " & strInstructors & _
strCourseTypeCondition & "q_jt_MCR_Instructor_Roles.[Course_TypesID] " & strCourseType & _
strRoleTypeCondition & "q_jt_MCR_Instructor_Roles.[Roles_ID] " & strRoleType & " AND " & "q_jt_MCR_Instructor_Roles.[Course_Date] " & strStartDate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("q_Parameter_Form").Command
cmd.CommandText = strSql
Set cat.Views("q_Parameter_Form").Command = cmd
Set cat = Nothing
' Open the Query
If Not IsNull(cboReports) And cboReports <> "" Then
DoCmd.OpenReport cboReports, acViewPreview ' use acNormal to print without preview
Else
MsgBox ("Please make a Label selection first from the dropdown list to the left.")
cboReports.SetFocus
End If
cboReports = ""
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
'Restore screen updating
cmdPreviewReports_Exit:
DoCmd.Echo True
Exit Sub
cmdPreviewReports_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdPreviewReports_Exit
End Sub
I am getting a syntax error with the first line of the
Code:
case IsDate(Me.Start_Date) and IsDate(me.End_Date) Then
Just trying to see if I can figure out what it doesn't like about that...