Filtered reports from a multi-select list

[pringb]

Hi,

I've got a form with 2 objects, one is a list of months, the other is a
multi-select list box.

eg.X (Month)

1,2,3

eg.Y (Multi-select List Box)
a,b,c

When the user select value X and mulitple values from Y I want to
generate a separate filtered report for each multi-select item and open
it eg . Rep1 filtered on Y-a,X-1 and rep2 filtered on Y-c,X-1,etc..

This is my code so far !

Dim varLoop As Variant

For Each varLoop In Y.ItemsSelected
strLinkCriteria = "([ReportFilterFieldY]) IN Me.Y.ItemData(varItem)"
AND ([ReportFilterFieldX]) IN (Forms!Formname!X)"
DoCmd.OpenReport "Reportname", acViewPreview, , strLinkCriteria
Next varLoop
End If

The code falls to pieces where I've got "IN Me.Y.ItemData(varItem)" - I
think I've got the right idea but can't get the syntax right.

Any help would be much appreciated

Bruce

 

[Tom Ellison]

Dear Bruce:

I'm going to assume the fields are both text.

Dim varItem AS Variant, strC AS String

For Each varItem In Y.ItemsSelected
strC = "ReportFilterFieldY = """ & Y.ItemData(varItem) & _
""" AND ReportFilterFieldX = """ & Me.X & """"
Debug.Pring strC ' temporary debug code - comment out when done
' DoCmd.OpenReport "Reportname", acViewPreview, , strC
Next varLoop

I have temporarily commented out the line to preview the report.
Let's just get the filters generated for now. When the filter string
looks good, then try opening reports.

If I didn't screw up the code, could you post back the results from
the immediate pane? Also, confirm whether the columns by which you
are filtering are text, date, or numeric.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts