ListBox Query

I need to run a query based on the the multi select option "extended" in a listbox. This listbox contains acct#'s and is in turn used in an report.


T.I.A
LVL 8
ampapaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianWrenCommented:
You are not going to be able to do this directly.  The multi selection can only be accessed sequentially, so you cannot get all of the values into the SQL easily.

You can, in code, make your way through the list, building up a string like:

AcctNum = val1 OR AcctNum = val2 OR AcctNum = val3 OR AcctNum = val4 . . .

concatenating " Or AcctNum = " & <value in the list" one at a time, then after processing all of the elements of the selection list, put "SELECT * FROM tblAccts WHERE " on the front of it, and ";" on the back, save it as a query, and then open your report based on that query.

Is that what you want to do?

Brian
0
jschrismanCommented:
You'll have to write a function which accepts the account number and returns true if it's selected in the listbox or false if it's not.

I've done things similar to this in the past. But, I stored the list box selections in a variable sized array and then ran queries off of that. That way the form didn't have to still be open while the queries were running.

Make any sense?
0
brewdogCommented:
An easier method than the "OR" stuff is to use the IN operator. Here's a sample of when I've done it:

Public Function BuildListBoxCriteria ()

    Dim strCriteria As String
    Dim i As Integer
    strCriteria = ""
    For i = 0 To lstDates.ItemsSelected.Count - 1
        strCriteria = strCriteria & "#" & lstDates.ItemData(i) & "#,"
    Next i
    strCriteria = "MyDate in (" & Left$(strCriteria, Len(strCriteria) - 1) & ")"
    DoCmd.OpenReport "My Report", acViewPreview, , strCriteria

End Function

Notice this is using a Date field in the list box and that I open the report directly from the function, rather than using it in a query. You could, of course, put the function in the criteria line in the query if you'd rather. If you are using a text field, replace "#" with chr(34); if you are using a number field, simply remove the "#" (and the extra &.

Hope that helps . . .

brewdog
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DedushkaCommented:
Hi all.

brewdog,
your code go through all items in listbox, so you should use For each... cycle, BTW

ampapa,
following code tested and work fine for me, so you can try it.

Private Sub cmdOpenQuery_Click()
Dim arCriteria() As String
Dim intTemp As Integer
Dim strIN As String
Dim strSQL As String
Dim strWHERE As String
Dim ctlList As Control, varItem As Variant

strSQL = "SELECT * FROM [tblAccounts]"

Select Case Me.List0.ItemsSelected.Count
    Case 0: strWHERE = ""
    Case Else
        ReDim arCriteria(Me.List0.ItemsSelected.Count)
        intTemp = 0
        ' Return Control object variable pointing to list box.
        Set ctlList = Me.List0
        ' Enumerate through selected items.
        For Each varItem In ctlList.ItemsSelected
            arCriteria(intTemp) = ctlList.ItemData(varItem)
            intTemp = intTemp + 1
        Next varItem
       
        'Join (a new function in VBA 6) returns a string
        'created by joining a number of substrings contained in an array.
        'Syntax: Join(sourcearray[, delimiter])
        strIN = Join(arCriteria, ", ")
        strIN = Left(strIN, Len(strIN) - 2)
        strWHERE = " WHERE [acct#] IN (" & strIN & ")"
End Select

strSQL = strSQL & strWHERE & ";"
MsgBox strSQL
End Sub

If your acct# field is text type use:
arCriteria(intTemp) = Chr(34) & ctlList.ItemData(varItem) & Chr(34)
instead of
arCriteria(intTemp) = ctlList.ItemData(varItem)

Good luck,
Dedushka
0
hnasrCommented:
' ampapa-

'This code in a button to click after making your multiselection, it will create the criteria to use with after WHERE
' eg:  mySql = "select * from table where " & criteria

'Button name:   Multiselect
'List Box name: myListBox
' String to hold join operator :AND_string, you can use or etc ... I used "=", you can use other comparisons.

' >>>
Private Sub multiselect_Click()
      Dim frm As Form, ctl As Control
      Dim varItm As Variant
      Dim criteria As String
      Dim AND_string As String

      Set frm = Forms!Form1
      Set ctl = frm!myListBox
      AND_string = ""
      criteria = ""
      For Each varItm In ctl.ItemsSelected
          criteria = criteria & AND_string & "myField = " & ctl.ItemData(varItm)
          AND_string = " AND "
      Next varItm
  End Sub
' <<<

' Feedback is apreciated.
' Good luck

0
brewdogCommented:
Thanks, Dedushka. I usually use the For Each. I copied and pasted this from a module -- obviously I didn't use the For Each there. :o)
0
ampapaAuthor Commented:
I have tried to make a hybrib select statement based on the replys I have received. Why doesn't this work? I get runtime 3306 at most one field can be returned errors use the "exists" keyword


Sub secondtry()
Dim ctl As Control
Dim strCriteria As String
Dim i As Variant

    Set ctl = Forms![Select Accounts for Report]![List28]
    strCriteria = ""
    For Each i In ctl.ItemsSelected
        strCriteria = strCriteria & "'" & ctl.ItemData(i) & "'" & ","
    Next i
    strCriteria = "Select * from Remaining_Not_Zero_Report where [acct#] = (" & Left$(strCriteria, Len(strCriteria) - 1) & ")"
    MsgBox strCriteria
    DoCmd.OpenReport "Remaining Exceptions", acPreview, , strCriteria
End Sub
0
brewdogCommented:
The error is because you are using the = in this line:

strCriteria = "Select * from Remaining_Not_Zero_Report where [acct#] = (" & Left$(strCriteria, Len(strCriteria) - 1) & ")"

with the , operator. If you use the , you have to use the In operator like I posted. If you want to use the =, you'll have to use Or instead of comma. Make sense?

Access assumes when it sees an = sign that there is only one value following.
0
ampapaAuthor Commented:
brewdog,
Changing the "=" to "in" also gives the same error? Thanks for the reply.
0
DedushkaCommented:
If your report based on saved Query:
SELECT * FROM Remaining_Not_Zero_Report

use this code:

Sub secondtry()
Dim ctl As Control
Dim strCriteria As String
Dim i As Variant

    Set ctl = Forms![Select Accounts for Report]![List28]
    strCriteria = "" 
    For Each i In ctl.ItemsSelected
        strCriteria = strCriteria & "'" & ctl.ItemData(i) & "'" & ","
    Next i
    strCriteria = "[acct#] IN (" & Left$(strCriteria, Len(strCriteria) - 1) & ")"
    'MsgBox strCriteria
    DoCmd.OpenReport "Remaining Exceptions", acPreview, , strCriteria
End Sub

Best regards,
Dedushka

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brewdogCommented:
Good catch, Dedushka. ampapa, if you are using strCriteria as your opening argument for the report, you don't need the whole "select" statement, just the where clause (the "[acct#] in" part). If you want to build the whole recordsource for the report in code, then keep what you have above, but remove the strCriteria and instead put this on the report's Open event:

me.recordsource = strCriteria

In this case, you'd need to move strCriteria to a generic module (from the modules tab) and declare it

Public strCriteria as string

so that it will be available to both the form and the report.
0
ampapaAuthor Commented:
Dedushka,

Works like a champ! Are the items listed in reverse order by default of the listbox or is that at the query level?

Thanks for all your help.
0
DedushkaCommented:
If you see items (Account numbers) in your report in reverse order you should set desired order in "Grouping and Sorting" in the report, because report has its own order not depending of query order level.
If you see reverse order in criteria (f.e. after MsgBox strCriteria) - it is a result of cycle:
For Each i In ctl.ItemsSelected
    strCriteria = strCriteria & "'" & ctl.ItemData(i) & "'" & ","
Next i

Thank you for accepting my answer.
BTW, I like the idea to collect all selected items into array and then just join them as shown in my first comment.

With my best wishes,
Dedushka
   
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.