Filtering a Report based on a list box

[jonosborne]

Hi, i have managed to filter a report based on selections made in a list box but am totally confused with a message box that appears everytime i run my report.

Let me explain (i apologise for being long-winded)...

I have a query called qry Queue Filtered SLA Position,
within this query there is a field 'Queue Group'.
I also have a report called rpt Queue Filtered SLA Position

I have a listbox called ListFilter. It's rowsource is equal to qry Queue Filtered SLA Position. The multiselect property is set to 'simple'.

I have the following code in my form :

Private Function GetCriteria() As String
   Dim stDocCriteria As String
   Dim VarItm As Variant
   For Each VarItm In ListFilter.ItemsSelected
  stDocCriteria = stDocCriteria & "[Queue Group] = " & ListFilter.Column(0, VarItm) & " OR "
   Next
   If stDocCriteria <> "" Then
  stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
   Else
  stDocCriteria = "True"
   End If
   GetCriteria = stDocCriteria
End Function
 

And a button to request the report which has the following code:

Private Sub Command2_Click()
DoCmd.OpenReport "rpt Queue Filtered SLA Position", acPreview, , GetCriteria()
End Sub
 

Now, when i make a selection from the listbox and click the button i get a message box entitled 'Enter Parameter Value' with the selection i have just made above the text box! If i then type in the selection i have made and click OK, the report is produced as intended, i.e. with just my selection.

Why, oh why does this message box appear ?? What is the point of making a selection only to have to type it in ?

Your comments/advise is greatly appreciated and needed.

[jonosborne]

I have a developement, i have realised that the problem is the 'Get Criteria()' part of the statement:

DoCmd.OpenReport "rpt Queue Filtered SLA Position", acPreview, , GetCriteria()

where the GetCriteria() is the WHERE condition. It is recognising the selection(s) as [selection] and not "selection" therefore is asking for the value.

However i am still at a loss as to how to rectify this, can anyone assist??

[kepston]

I have had trouble building string criteria in the past too.
In this case I think you need to put in "" to add quotes around your listbox values.

stDocCriteria = stDocCriteria & "[Queue Group] = """ & ListFilter.Column(0, VarItm) & """ OR "

I hope that's clear!

[jonosborne]

I have had trouble building string criteria in the past too.
In this case I think you need to put in "" to add quotes around your listbox values.

stDocCriteria = stDocCriteria & "[Queue Group] = """ & ListFilter.Column(0, VarItm) & """ OR "

I hope that's clear!

Thank you very much, i knew it had to recognise it as a string and have just had chance to check this out before leaving work; great...it works!!!.

Cheers

[NeoPa]

That's a good answer, to double up the quotes (""), but a better answer for working in SQL is to use the single-quotes (') instead for strings (NB ONLY in SQL - VBA doesn't work with them).

[NeoPa]

I found this link to clarify further (Quotes (') and Double-Quotes (") - Where and When to use them.).