Help with Filter & FilterON

[EAB1977]

Can anyone tell me why this code is not working? It opens the report
fine but does not enforce the filters.

Private Sub btnCost_Click()
DoCmd.OpenReport "rptCost", acViewPreview
Reports("rptCost").Filter = Me.lstResults.Column(0)
Reports("rptCost").FilterOn = True
End Sub

 

[Dirk Goldgar]

Can anyone tell me why this code is not working? It opens the report
fine but does not enforce the filters.

Private Sub btnCost_Click()
DoCmd.OpenReport "rptCost", acViewPreview
Reports("rptCost").Filter = Me.lstResults.Column(0)
Reports("rptCost").FilterOn = True
End Sub

I'm not sure what's wrong -- what is the value of lstResults.Column(0) ?

I would normally do this via the Where-Condition argument of the OpenReport
method:

DoCmd.OpenReport "rptCost", acViewPreview, _
WhereCondition:=Me.lstResults.Column(0)

But either way, Me.lstResults.Column(0) must evaluate to a valid
where-condition.

 

[John W. Vinson]

Can anyone tell me why this code is not working? It opens the report
fine but does not enforce the filters.

Private Sub btnCost_Click()
DoCmd.OpenReport "rptCost", acViewPreview
Reports("rptCost").Filter = Me.lstResults.Column(0)
Reports("rptCost").FilterOn = True
End Sub

You're opening the report unfiltered, displaying the unfiltered result, and
THEN setting the filters. Try passing the filter arguments in the OpenReport
method instead. Note that the filter must be a valid SQL WHERE clause, with a
fieldname and a value (e.g. "[CompanyID] = 3145"); unless your listbox
contains such strings it's not going to work.

Try something like

DoCmd.OpenReport "rptCost", acViewPreview, WhereCondition := "[fieldname] = "
& Me!lstResults.Column(0)

and leave the Filter properties alone.