Report Filter used to Pass Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a report that I am using the report filter to filter the data. My
filter is as follows:

issue=[Enter Issue] and Year = [Enter Year] and SWNS = true

Instead of SWNS field being hardcoded, I want to pass the field name via a
parameter. Sometimes I want the report to run with SWNS=true and sometimes
CSW=true. How do I turn the string I am passing into a field?

I would greatly appreciate any help.

Sarah
 
Use a form with controls to enter the parameters, and a command button to
open the report. You can then use the WhereCondition of the OpenReport
action to include only the fields you want to filter.

This example assumes the form has these unbound controls:
- a text box named txtIssue, for entering the issue.
- a text box named txtYear, for entering the year.
- a check box named chkSWNS. (If checked, only the True records are
returned; if unchecked the SWNS field is not used in selecting critiera.)
- a command button named cmdPrint, for opening the report.

The Event Procedure to use in the Click event of the command button looks
something like the example below. It ignores any blank boxes. It tacks " AND
" on the end of each condition ready for the next one, and then chops the
trailing " AND " off at the end. (This makes it really easy to add more
conditions if you wish.)

Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtIssue) Then
strWhere = strWhere & "([issue] = " & Me.txtIssue & ") AND "
End If

If Not IsNull(Me.txtYear) Then
strWhere = strWhere & "([Year] = " & Me.txtYear & ") AND "
End If

If Me.chkSWNS = True Then
strWhere = strWhere & "([SWNS] = True) AND "
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Notes:
1. If [issue] is a Text type field (not a Number type field), you need extra
quotes:
strWhere = strWhere & "([issue] = """ & Me.txtIssue & """) AND "

2. VBA has a function call Year, so Year is not a good name for a field. I
think you will get away with it with this particular code, but keep it in
mind if you do have Access misunderstanding what you intend.
 
Back
Top