Using a multiselected listbox as a filter for a report.

[mgid]

I am trying to have a report open with one of the fields filtered with user-selected criteria in a list box with the multiselect property set to simple.

I've managed to get the selections into a string using the ItemsSelected and ItemData properties. Now I'm trying to pass the string as a variable in the Wherecondition argument of the Docmd.OpenReport command. It seems that the Wherecondition clause will not take the variable. Maybe my entire approach is off? Any help would be appreciated. [sig][/sig]

 

[kathryn]

Could you please post the code from where you create the string to where you are using it in the OpenReport command?
[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[LarryDeLaruelle]

gmiha:

I think you are on the right track. Your problem is probably trying to pass the entire group of criteria as one where string. I had a similar problem and, based on the help I got here at Tek Tips, I had to create a query string that used the OR condition.

strWhere = &quot;WHERE FieldName ='Value1' OR FieldName = 'Value2'&quot;

This is a pretty simplistic example but, hopefully, will give you an idea of what is needed.

Note: Because of the way Access translates the SQL string, each Where criteria represents a separate line (in QBE) so all additional criteria (if any) need to be incorporated in each where condition. (Hope that makes sense.)

Good luck.
[sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[mgid]

Here's the code where I'm using the variable:

DoCmd.OpenReport (&quot;rptProjectSummary&quot, View:=acViewPreview, Wherecondition:=&quot;[Type] = strItem&quot;

I keep getting an &quot;Enter parameter&quot; inputbox that asks for strItem. Maybe my syntax is wrong?

strItem comes from:

Dim X As Variant, strFilterType As String

For Each X In Forms!frmReportSelection!lstTypeSummary.ItemsSelected
strFilterType = strFilterType & Forms!frmReportSelection!lstTypeSummary.Column(0, X) & &quot;;&quot;
Next X [sig][/sig]

 

[MichaelRed]

DoCmd.OpenReport (&quot;rptProjectSummary&quot, View:=acViewPreview, Wherecondition:=&quot;[Type] = &quot; & Chr(34) & strItem & Chr(34)

At least as far as the actual 'Where'.

I don't see how strItem is generated from the post, but it does not look correct, as you are adding the semi-colon to each part of the strFilterType variable - this is a not the norm for most Ms. Access lists/accumulations.


[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[mgid]

MichaelRed and LarryDeLaruelle,

Thanks for the help. I managed to get this code to work:

Dim X As Variant, strType As String

For Each X In Forms!frmReportSelection!lstTypeSummary.ItemsSelected

strType = strType & &quot;[Type] =&quot; & Chr(34) & Forms!frmReportSelection!lstTypeSummary.Column(0, X) & Chr(34) & &quot; OR &quot;

Next X

strType = Left(strType, Len(strType) - 4)

DoCmd.OpenReport (&quot;rptProjectSummary&quot, View:=acViewPreview, Wherecondition:=strType [sig][/sig]