Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: Multiple Multi-Select List Boxes Query

    Hi,

    I am trying to run a parameter Query based off of several list boxes. I want to select all for every item in the listbox, and have found some very helpful code to get started


    I am having trouble with the WHERE portion of the SQL statement.

    Here is what I have so far?

    strSQL = "SELECT* FROM MS " & _
    "WHERE MS.MSA IN(" & strCriteria1 & ") and MS.facind IN (" &strCriteria2& ") and MS.specind in (" &StrCriteria3&")" ;"


    Anyone know how to format this?


    Thank you in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. When using IN, every element must be separated by a comma (,). e.g.
    Code:
    IN ( 1, 3 5 8 )
    2. If the elements in the enumeration are not numeric, they must be each enclosed in quotes (' or ") e.g.
    Code:
    IN ( 'a', 'b', 'v' )
    3. In your example, you use AND as the conjuction operator between the different conditions. Be sure that you want the query to return the rows that match the three criteria simultaneously (i.e. dataset matches Criteria1 and also matches Criteria2 and also matches Criteriia3). Otherwise, the operator should be OR.
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    Thanks,

    Your suggestion for the OR was indeed what I wanted

    I incorporated apostrophe's using the following code:

    For Each varItem In Me!MSA_List.ItemsSelected
    strCriteria1 = strCriteria1 & ",'" & Me!MSA_List.ItemData(varItem) & "'"
    Next varItem

    strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)


    and used the following sql

    strSQL = "SELECT* FROM MS " & _
    "WHERE MS.MSA IN (" & strCriteria1 & ") OR MS.facind IN (" &strCriteria2& ") OR MS.specind IN (" &StrCriteria3&"); "


    However, I am getting a syntax error.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The concatenation operator (&) must be separated from the strings (or part of strings) it joins with spaces and there must be a space between SELECT and *, so it should be:
    Code:
    strSQL = "SELECT * FROM MS " & _
    "WHERE MS.MSA IN (" & strCriteria1 & ") OR MS.facind IN (" & strCriteria2 & ") OR MS.specind IN (" & StrCriteria3 & ");"
    2.If the error occurs because of the SQL expression (strSQL), you need to be able to check what's wrong with the expression:
    - change the code to:
    Code:
    strSQL = "SELECT * FROM MS " &  _
    "WHERE MS.MSA IN (" & strCriteria1 & ") OR MS.facind IN (" & strCriteria2 & ") OR MS.specind IN (" & StrCriteria3 & ");"
    Debug.Print strSQL
    Stop
    - execute the code and, when it stops, open the immediate window (Ctrl+G) and see what the contents of strSQL looks like.
    - if you cannot spot anything wrong, copy the SQL expression from the imediate window, create a new query, switch it to SQL view and paste the SQL expression into it. Then, try to switch the query to datasheet view. The error message you'll receive is usually more explicit than the one reported by the VBA interpretor.

    3. If nothing works, please post the SQL expression (see here above), as well as the metadata of the table(s) being involved in the query: name(s), columns names, data types.
    Have a nice day!

  5. #5
    Join Date
    Apr 2014
    Posts
    4
    HI,

    I was able to get it to work. Here is what I used:

    strSQL = "SELECT MS.*,MS.MSA,MS.facind,MS.specind FROM MS " & _
    "WHERE MS.MSA IN (" & strMSA & ") and MS.facind IN (" & strFacility & ") and MS.specind IN (" & strPrimary & "); "


    Thanks for your help!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •