Need help with a form that dictates report results

[markarmer]

I created a form for viewing animal medical records using the code below which has two parts to it - the first part allows me to pull up the report between two dates (the code for this I adapted from Allen Browns website, so thank you for that) which shows eveything between the dates for ALL animals, the second allows me to base the report on an individual animal but it shows an entire history of records for that animal, not based on a date range. What I need to do is keep the two forementioned as they both have their place and both work perfectly, however I also need the ability to combine them so I can pull my report based on an individual animal BUT only within the date range specified...

I have been trying to play with the Where functionality and add it to Allen's code but just can't get it to work.

Any help on this would be very gratefully received as the answer will allow me to adapt it for other reports on my database also. Thank you in advance for any assistance with this,

Now here is the code (I seperated the two parts with -----):

Option Compare Database
Option Explicit
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "Medical1" 'Put your report name in these quotes.
strDateField = "[Medical_Date]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport 'Use acViewNormal to print instead of preview.

'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
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
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 Sub
-----------------------------------------------


Private Sub Command16_Click()
DoCmd.OpenReport "Medical1", acViewReport, , "Animal_Name = '" & Me.cboName & "'"
End Sub

[ssanfu]

Try this:

Option Compare Database
Option Explicit

Private Sub cmdPreview_Click()
  'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
  'Purpose: Filter a report to a date range.
  'Documentation: http://allenbrowne.com/casu-08.html
  'Note: Filter uses "less than the next day" in case the field has a time component.
  Dim strReport As String
  Dim strDateField As String
  Dim strAnimalName As String
  Dim strWhere As String
  Dim lngView As Long
  Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.

  'DO set the values in the next 3 lines.
  strReport = "Medical1"  'Put your report name in these quotes.
  strDateField = "[Medical_Date]"  'Put your field name in the square brackets in these quotes.
  strAnimalName = "[Animal_Name]"
  lngView = acViewReport  'Use acViewNormal to print instead of preview.

  'Build the filter string.
  'start date
  If IsDate(Me.txtStartDate) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
  End If
  'end date
  If IsDate(Me.txtEndDate) Then
    If strWhere <> vbNullString Then
      strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
  End If

  'animal name
  If Len(Trim(Me.cboName & "")) > 0 Then
    If strWhere <> vbNullString Then
      strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strAnimalName & " = '" & Me.cboName & "')"
  End If

  'Close the report if already open: otherwise it won't filter properly.
  If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
  End If

  'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.

  'Open the report.
  DoCmd.openreport strReport, lngView, , strWhere
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 Sub

[markarmer]

Thank You VERY much SSANFU, that worked perfectly!

[ssanfu]

Great! Are you ready to make this solved??