Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2012
    Posts
    31

    Unanswered: Parsing listbox selection and passing as Criteria

    Searching the interent for my question bought me here. I could find similiar questions on this forum but nothing that's worked so far. Hoping someone here knows how to do this.

    I have a form with a list box named lstBranch. The multi select property is set to simple. What I'm hoping to do is use those multiple selections in a query.

    What I'd done so far is combine all of the selections into one string, pass that string to a global variable. Then a function is called in the query return that global variable . When I only select one item from the list box the query runs fine. When I select more than one item, nothing is return by the query.

    From the module
    Code:
    Public g_BRANCHSELECT  As String
    
    Public Function GetLocations() As String
    GetLocations = g_BRANCHSELECT
    End Function
    From the click event on the form
    Code:
    Dim intIndex As Integer
    Dim intCount As Integer
    Dim strBranchSelect As String
    
    intCount = lstBranch.ListCount - 1
    strBranchSelect = ""
    
    For intIndex = 0 To intCount
    
        If lstBranch.Selected(intIndex) Then
            strBranchSelect = strBranchSelect & Trim(lstBranch.Column(0, intIndex)) & " Or "
        End If
    
    Next
    
    If strBranchSelect = "" Then
        MsgBox ("You need to select atleast one Location")
    Else
        strBranchSelect = Left(strBranchSelect, (Len(strBranchSelect) - 4))
        g_BRANCHSELECT = strBranchSelect
        DoCmd.OpenQuery ("qTop100")
    
    End If
    I've tried adding the equal sign to the string but then the query doesn't return anything regardless of how many slected item I have. I'm not too familair with SQL, maybe adding a 'distinct', or simple select * where tbl = 'something'. If I where more familiar with Sql I might have tried to go that route. Any ideas would be great. Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL statement of the query [qTop100].
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    31
    This is qTop100
    Code:
    SELECT dbo_rARInvHistory.chst_customer, dbo_rARInvHistory.chst_date, dbo_rARInvHistory.chst_division, IIf([chst_type]="CR",[chst_amount]*-1,[chst_amount]) AS Amount, IIf([chst_type]="CR",[chst_gpcost]*-1,[chst_gpcost]) AS GPCost
    FROM dbo_rARInvHistory
    GROUP BY dbo_rARInvHistory.chst_customer, dbo_rARInvHistory.chst_date, dbo_rARInvHistory.chst_division, IIf([chst_type]="CR",[chst_amount]*-1,[chst_amount]), IIf([chst_type]="CR",[chst_gpcost]*-1,[chst_gpcost]), dbo_rARInvHistory.chst_type
    HAVING (((dbo_rARInvHistory.chst_date)>=[Forms]![frmTop100]![txtStartdate] And (dbo_rARInvHistory.chst_date)<=[Forms]![frmTop100]![txtEndDate]) AND ((dbo_rARInvHistory.chst_division)=getlocations()));

  4. #4
    Join Date
    Aug 2012
    Posts
    31
    This might be a simpler to follow.

    Code:
    SELECT dbo_rARInvHistory.chst_customer, dbo_rARInvHistory.chst_division
    FROM dbo_rARInvHistory
    WHERE (((dbo_rARInvHistory.chst_division)=getlocations()));

  5. #5
    Join Date
    Aug 2012
    Posts
    31
    Reading through related posts in this forum, it looks like I need to write the sql quries as a string and pass that to the engine, but i'm not sure how to go that ... or if I even said that right.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try changing the code like this:
    Code:
    Sub Click()
    
        Const c_SQL As String = "SELECT dbo_rARInvHistory.chst_customer, dbo_rARInvHistory.chst_division " & _
                                "FROM dbo_rARInvHistory " & _
                                "WHERE (((dbo_rARInvHistory.chst_division) IN (@L)));"
        Dim intIndex As Integer
        Dim strBranchSelect As String
        Dim qdf As DAO.QueryDef
    
        For intIndex = 0 To lstBranch.ListCount - 1
            If lstBranch.Selected(intIndex) Then
                If strBranchSelect <> "" Then strBranchSelect = strBranchSelect & ", "
                strBranchSelect = strBranchSelect & Trim(lstBranch.Column(0, intIndex))
            End If
        Next
        If strBranchSelect = "" Then
            MsgBox ("You need to select atleast one Location")
        Else
        
            ' The query [qTop100] must already exist.
            '
            Set qdf = CurrentDb.QueryDefs("qTop100")
            qdf.SQL = Replace(c_SQL, "@L", strBranchSelect)
            qdf.Close
            DoCmd.OpenQuery ("qTop100")
        End If
    
    End Sub
    Note: You don't need the function GetLocations() any more.
    Have a nice day!

  7. #7
    Join Date
    Aug 2012
    Posts
    31
    THank you for the help.

    It worked the way it was, but there is one part I didn't understanding

    in this statement


    Code:
             If strBranchSelect <> "" Then strBranchSelect = strBranchSelect & ","
                strBranchSelect = strBranchSelect & "'" & Trim(lstBranch.Column(0, intIndex)) & "'"
            End If
    It seems like when the first runs, if strBranchSelect equal anything then add a comma, then add the string from the listbox. But that would make it start with a comma and that is not what happens. Can you enlighten me here? Why doesn't start with a comma?

    I had to change the constant to make it work for my situation. but that was easier than I thought after you gave me a working method. Thanks again.

    Code:
    Const c_SQL As String = "SELECT dbo_rARInvHistory.chst_customer, dbo_rARInvHistory.chst_date, dbo_rARInvHistory." & _
        "chst_division, IIf([chst_type]='CR',[chst_amount]*-1,[chst_amount]) AS Amount, " & _
        "IIf([chst_type]='CR',[chst_gpcost]*-1,[chst_gpcost]) AS GPCost " & _
        "FROM dbo_rARInvHistory " & _
        "Where (((dbo_rARInvHistory.chst_date)>=[Forms]![frmTop100]![txtStartdate] And (dbo_rARIn" & _
        "vHistory.chst_date)<=[Forms]![frmTop100]![txtEndDate]) AND " & _
        "((dbo_rARInvHistory.chst_division) " & "IN (@L)));"

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by profector View Post
    It seems like when the first runs, if strBranchSelect equal anything then add a comma, then add the string from the listbox. But that would make it start with a comma and that is not what happens. Can you enlighten me here? Why doesn't start with a comma?
    Because the string strBranchSelect is supposed to be something like (random values): "14, 17, 21, 44". When the loop starts (first iteration) the string is empty (strBranchSelect = ""), so no comma is added to it.

    This string is then substituded to the placeholder @L by the Replace() function. This yields something like:

    [/CODE]... AND ((dbo_rARInvHistory.chst_division) IN (14, 17, 21, 44)));"[/CODE]
    Have a nice day!

  9. #9
    Join Date
    Aug 2012
    Posts
    31
    Thanks for sticking with me on this. The code is working now and I've started working on something new, but I would like to understand why there were two if's and only one end if and no error message.

    I dont think I've ever seen a if then without an end if like you did.

    Code:
            If lstBranch.Selected(intIndex) Then
                If strBranchSelect <> "" Then strBranchSelect = strBranchSelect & ","
                strBranchSelect = strBranchSelect & "'" & Trim(lstBranch.Column(0, intIndex)) & "'"
            End If

  10. #10
    Join Date
    Aug 2012
    Posts
    31
    NM I got it. I just never seen it that way before.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This:
    Code:
    If lstBranch.Selected(intIndex) Then
        If strBranchSelect <> "" Then strBranchSelect = strBranchSelect & ","
        strBranchSelect = strBranchSelect & "'" & Trim(lstBranch.Column(0, intIndex)) & "'"
    End If
    Is functionally the same as:
    Code:
    If lstBranch.Selected(intIndex) Then
        If strBranchSelect <> "" Then 
            strBranchSelect = strBranchSelect & ","
        End If
        strBranchSelect = strBranchSelect & "'" & Trim(lstBranch.Column(0, intIndex)) & "'"
    End If
    The syntax of the BASIC language (VBA derives from it) specifies that you can write a one-line If statement (i.e. If <Condition> + <what to do when the condition is True>) on a single line and without an ending End If statement.

    Some people (purists?) recommend not to use it but I'm not among them, as I don't consider that it makes the code less readable. Just a question of habits, I guess.
    Have a nice day!

  12. #12
    Join Date
    Aug 2012
    Posts
    31
    Purist haha... I'm not one of them so I think i'll you it when I can.

Posting Permissions

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