Add date Range to a Built sql string Parameter form

[Oblio]

I cannot modify my parameter form to allow for a date range selection. I have tried so many different combinations I am at a loss for where to start.

Is anyone able to help me with this as I am so frustrated having spent 2 days on trying to figure out something which must be so easy to do?????

I tried adding this in various forms with no success... I just could not get it to pass the date range properly into the built in sql:

 If Len(Me.Start_Date & vbNullString) > 0 Then
    If IsDate(Me.Start_Date) Then
      strSQL = strSQL & "MyDateField >= " & Format(Me.Start_Date, "\#yyyy\-mm\-dd\#") & " AND "
    End If
  End If
  If Len(Me.End_Date & vbNullString) > 0 Then
    If IsDate(Me.End_Date) Then
      strSQL = strSQL & "MyDateField <= " & Format(Me.End_Date, "\#yyyy\-mm\-dd\#") & " AND "
    End If
  End If]

I say UNCLE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[ranman256]

you shouldnt need format(...)
but normally its: Format(Me.Start_Date, "yyyy\mm\dd")

select case true

 case IsDate(Me.Start_Date) and IsDate(me.End_Date) Then
      strSQL = strSQL & "MyDateField between #" & Me.Start_Date & "# AND "#" & Me.End_Date & "#"

 case IsNull(Me.Start_Date) and IsDate(me.End_Date) Then
      strSQL = strSQL & "MyDateField <= #" & Me.End_Date & "#"

 case IsDate(Me.Start_Date) and IsNull(me.End_Date) Then
      strSQL = strSQL & "MyDateField >= #" & Me.Start_Date & "#
End select

[ranman256]

duplication of above

[Oblio]

Than you so much for helping me with this...I am really unsure of where I should add this to the code I have so that the whole thing gets built... would you be able to show me where it would be inserted as I had no luck placing it prior to the Instructor section or after the role section....
Again thank you for your guidance with this !!!

[Oblio]

Here is how I have placed the code you provided in the whole thing:

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

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...

[ranman256]

this is complicated for a simple query.
but the syntax error could be either the textbox names are incorrect,
or the code above is not in the form with the textboxes.

I would think the Case statmt is correct but if my above items are fine then try
case (IsDate(Me.Start_Date) and IsDate(me.End_Date)) Then

[Oblio]

I am getting Syntax error "expected end of statement" and it has highlighted the Then