Reports based on variables

  • Thread starter Thread starter rascal
  • Start date Start date
R

rascal

I have a form with 3 drop down boxes called employee, supervisor and month,
that pulls up a report. All 3 fields must be populated for that report.
What my manager would like is to be able to select any or all of them and
pull the report. For example if she chose a particular supervisor then she
would like to see all the records no matter how many employees or dates there
are, or chose supervisor and month and see those records or employee and
month and see those records. Is there a way to do this.
Thanks
 
I generally try to NOT place criteria in the Record Source of a report. It is
much more flexible if you can build a Where Condition with code. For
instance, the code to open your report might look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.Employee) Then
strWhere = strWhere & " AND [EmployeeID] = " & Me.Employee
End If
If Not IsNull(Me.Supervisor) Then
strWhere = strWhere & " AND [SuperID] = " & Me.Supervisor
End If
If Not IsNull(Me.[Month]) Then
strWhere = strWhere & " AND Month([DateField]) = " & _
Me.[Month]
End If
DoCmd.OpenReport "rptReportName", acPreview, , strWhere

If your fields are text rather than numeric, you will need to use something
like:
If Not IsNull(Me.Employee) Then
strWhere = strWhere & " AND [Employee] = """ & Me.Employee & """ "
End If
 
Back
Top