Reports based on variables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rascal - 06 Mar 2008 21:49 GMT

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

Reply to this Message

Duane Hookom - 06 Mar 2008 22:43 GMT

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

Signature

Duane Hookom
Microsoft Access MVP

> 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.
[quoted text clipped - 4 lines]
> month and see those records. Is there a way to do this.
> Thanks

Reply to this Message