Reports based on variables

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
 
D

Duane Hookom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box on Parameter Form 4
Help on report 2
Conditional Sum based on Date Range 1
HR Report 3
Dates and sub reports 5
An Opposite Report 2
Conditional reference? 1
Rpt and SubRpts with dates 4

Top