Filtering a report with multiple user-defined parameters

[Pharma Down]

Having just tried this I know that it doesn't work...

 

[pbaldy]

Try

If Not IsDate(Me.txtFromDate) Then

 

[Pharma Down]

Do you mean
If Not IsDate(Me.txtFromDate) Then
MsgBox "Please enter a start date for the report period"
Exit Sub
End If

Does that just mean if the field txtFromDate is empty then create the message box?

 

[Pharma Down]

I tried this and it threw up the error message like before (see post #18)!

 

[pbaldy]

Really? It works fine in your earlier db for me:

  Dim strWhere                As String
  Dim ctl                     As Control
  Dim varItem                 As Variant

  'make sure a selection has been made
  If Me.lstEmployees.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If

  If Not IsDate(Me.txtFromDate) Then
    MsgBox "Please enter a start date for the report period"
    Exit Sub
  End If
  
  If Not IsDate(Me.txtToDate) Then
    MsgBox "Please enter an end date for the report period"
    Exit Sub
  End If
  'add selected values to string
  Set ctl = Me.lstEmployees
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
  DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#"

 

[Pharma Down]

I cut and pasted your code and it worked perfectly!

I had stuck the
If Not IsDate(Me.txtFromDate) Then
MsgBox "Please enter a start date for the report period"
Exit Sub
End If
at the end, after the DoCmd.OpenRpt but, before Exit_cmdOpenReport_Click:
Exit Sub

It obviously matters where you put the code... and I picked the wrong place!

 

[Pharma Down]

So, to create my own version, do I just create a form with these bits (list box and date fields) and then cut and paste all the code?

 

[pbaldy]

I worked off the sample you provided in post 16.

 

[Pharma Down]

I have failed to get this to work completely so far... I think it is something to do with what I put here:
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(...
I think that my problem comes from how I made the report. I am going to try again tonight with a new report.

However, since I am handling many reports in this way, if I want to filter just by dates, which bits of the code do I need or can leave out?


Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
If Not IsDate(Me.txtFromDate) Then
MsgBox "Please enter a start date for the report period"
Exit Sub
End If

If Not IsDate(Me.txtToDate) Then
MsgBox "Please enter an end date for the report period"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#"

 

[pbaldy]

All I can say is it works perfectly in your sample. To only do the date, delete anything to do with the listbox and start the wherecondition at the first DateOfBirth instead of EmpID.

 

[Pharma Down]

Yes - works perfectly with the sample. The problems came when I tried to apply it to my own database instead of the example that I downloaded from http://baldyweb.com/multiselect.htm!

My problem with deleting anything to do with the listbox is not knowing what does relate to it. If I delete the EmpID bit, I'll hazard a guess at
Option Compare Database
Option Explicit
----------------
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

If Not IsDate(Me.txtFromDate) Then
MsgBox "Please enter a start date for the report period"
Exit Sub
End If

If Not IsDate(Me.txtToDate) Then
MsgBox "Please enter an end date for the report period"
Exit Sub
End If


DoCmd.OpenReport "rptEmployees", acPreview, , DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#"

Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

...but since I don't know what any of the code really means or does then I don't know if I'm missing important chunks out. Everything I have tried so far doesn't work. Am I missing some dims, strings and ctls?

Andy

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#" Yesterday 01:10 PM

 

[Pat Hartman]

I didn't read the whole thread but I did notice a problem in your original question. The Me.Name reference in:
DoCmd.OpenReport "Rpt_interventions_all", , , "FieldName = '" & Me.Name & "'"
is referring to the name of the open form. It is not referring to a field named "name". This exemplifies one of the problems caused by using property/method/function names as column names and that is confusion in VBA. You would need to use Me.[Name] to indicate that you were referring to a column name or control rather than the Name property ofthe Me object.

 

[Pharma Down]

Are you saying that using the fieldname "Name" is worng because it might mean something elese to Access?
...but I can get around this mistake by putting the fieldname in [ ]?


I think that the amended code from pbaldy is likely to do the job better for what I want... I'm just having an issue getting it to work consistently.

A

 

[Pat Hartman]

Yes. "Name", "Date", "Month", "Caption", "Field", etc. are all poor choices for column names because they are all the name of a property or function. You can get away with using them if you understand how to disambiguate the reference. Enclosing the names in square brackets tells Access that the name is something you defined rather than something intrinsic.