Filter report on Selcted Details

[AussieGal]

Hi,



I have this code that works well on another form I am using, and I am trying to edit it to work on a new form to do a similar task. I want a user to select the cmdSelectEpisode from the first selection box, then I want them to enter dates that they want to run the report in the txtstartdate and txtenddate unbound fields. When the click on the cmdEditEpisode button I am trying to make a report appear that only shows records that match the first criteria selected and to run between the 2 dates selected, but at the moment I cant get it to filter on the dates as well, only the first selected criteria. I am unsure how to get 3 criteria to work together.

Private Sub cmdEditEpisode_Click()If IsNull(Me.cmdSelectEpisode) Then
 MsgBox "You must select an episode that you want to edit first", vbOKOnly, "Must Select Client"
 Else
      Me.Visible = False
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    strDateField = "[ServiceDate]"
    lngView = acViewPreview
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
      End If
    'Open the report.
     DoCmd.OpenReport "rptProgressNotes", acViewReport, , "EpisodeID=" & Me.cmdSelectEpisode
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End If
End Sub

[June7]

What is name of the date field in the report RecordSource?

DoCmd.OpenReport "rptProgressNotes", acViewReport, , "EpisodeID=" & Me.cmdSelectEpisode & " AND datefieldname BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

If you want to make the dates optional then use code to construct string into the strWhere variable and concatentate.

DoCmd.OpenReport "rptProgressNotes", acViewReport, , "EpisodeID=" & Me.cmdSelectEpisode & strWhere