By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

Filtering a Report based on a list box

P: 34
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 :

Expand|Select|Wrap|Line Numbers
  1. Private Function GetCriteria() As String
  2.    Dim stDocCriteria As String
  3.    Dim VarItm As Variant
  4.    For Each VarItm In ListFilter.ItemsSelected
  5.   stDocCriteria = stDocCriteria & "[Queue Group] = " & ListFilter.Column(0, VarItm) & " OR "
  6.    Next
  7.    If stDocCriteria <> "" Then
  8.   stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
  9.    Else
  10.   stDocCriteria = "True"
  11.    End If
  12.    GetCriteria = stDocCriteria
  13. End Function
  14.  
And a button to request the report which has the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2. DoCmd.OpenReport "rpt Queue Filtered SLA Position", acPreview, , GetCriteria()
  3. End Sub
  4.  
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.
Jun 26 '07 #1
Share this Question
Share on Google+
5 Replies


P: 34
I have a developement, i have realised that the problem is the 'Get Criteria()' part of the statement:
Expand|Select|Wrap|Line Numbers
  1. 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??
Jun 26 '07 #2

Expert
P: 97
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.
Expand|Select|Wrap|Line Numbers
  1. stDocCriteria = stDocCriteria & "[Queue Group] = """ & ListFilter.Column(0, VarItm) & """ OR "
I hope that's clear!
Jun 26 '07 #3

P: 34
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 26 '07 #4

NeoPa
Expert Mod 15k+
P: 31,429
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).
Jul 3 '07 #5

NeoPa
Expert Mod 15k+
P: 31,429
I found this link to clarify further (Quotes (') and Double-Quotes (") - Where and When to use them.).
Jul 3 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.