Results 1 to 5 of 5
  1. #1
    KiEESH is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    4

    Using Multiselect Listbox to pass criteria to Query


    Hi!

    Working on a similar project where users can make selections from one of two listboxes to run a query.

    My code worked fine for just one listbox ('Phases').

    Phase is numerical and Zone is text.

    Having trouble incorporating the right code to have Access check one list OR the other ('Zones').

    The trouble started when I added 'Zones' into the mix.

    My latest attempt returns an error message "Data Type Mismatch."


    Any advice is appreciated.

    Code:
    Option Compare Database
    Private Sub btn_RunAdHocQry_Click()
        On Error GoTo Err_btn_RunAdHocQry_Click
        Dim MyDB As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strIN As String
        Dim flgSelectAll As Boolean
        Dim varItem As Variant
        '-----------------------------------------------------------------------------
           Set MyDB = CurrentDb()
        '------------------------------------------------------------------------------
        strSQL = "SELECT * FROM AREA_GROWTH2"
        '------------------------------------------------------------------------------
        'Build the IN string by looping through the listbox
        For i = 0 To lst_AdHocPhase.ListCount - 1 Or lst_Zone.ListCount - 1
            If lst_AdHocPhase.Selected(i) Or lst_Zone.Selected(i) Then
                If lst_AdHocPhase.Column(0, i) = "All" Or lst_Zone.Column(0, i) = "All" Then
                    flgSelectAll = True
                End If
                strIN = strIN & "'" & lst_AdHocPhase.Column(0, i) & "'," Or strIN = strIN & "'" & lst_Zone.Column(0, i) & "',"
            End If
        Next i
        
            '------------------------------------------------------------------------------
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase] Or [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
        '------------------------------------------------------------------------------
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            strSQL = strSQL & strWhere
        End If
        '------------------------------------------------------------------------------
        MyDB.QueryDefs.Delete "qry_AdHoc"
        Set qdef = MyDB.CreateQueryDef("qry_AdHoc", strSQL)
        '------------------------------------------------------------------------------
        'Open the query, built using the IN clause to set the criteria
        DoCmd.OpenQuery "qry_AdHoc", acViewNormal
        DoCmd.Close
        strfilename = "C:\Documents and Settings\" & Environ("username") & "\Desktop\AGDB_AdHocQuery.xls "
        DoCmd.TransferSpreadsheet acExport, , "qry_AdHoc", strfilename
        MsgBox "Successful Export to Desktop", , "File Export"
        
        '------------------------------------------------------------------------------
        
        'Clear listbox selection after running query
        For Each varItem In Me.lst_AdHocPhase.ItemsSelected
            Me.lst_AdHocPhase.Selected(varItem) = False
        Next varItem
        
        '-------------------------------------------------------------------------------
       
        
    Exit_btn_RunAdHocQry_Click:
        Exit Sub
    Err_btn_RunAdHocQry_Click:
        If Err.Number = 5 Then
            MsgBox "You must make a selection from the list" _
                   , , "Selection Required !"
            Resume Exit_btn_RunAdHocQry_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_btn_RunAdHocQry_Click
        End If
    
    End Sub

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I think that your WHERE clause needs to changed from
    Code:
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase] Or [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
    to
    Code:
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase]  in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")" Or [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
    I think that each criteria needs to be spelled out.

    To ensure that SQL works in VBA, I often build the query using the QBE and test and then copy the SQL statement to the VBA

  3. #3
    KiEESH is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    4
    Quote Originally Posted by alansidman View Post
    I think that your WHERE clause needs to changed from
    Code:
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase] Or [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
    to
    Code:
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase]  in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")" Or [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
    I think that each criteria needs to be spelled out.

    To ensure that SQL works in VBA, I often build the query using the QBE and test and then copy the SQL statement to the VBA



    That didn't work. Got a compile error ("Expected: end of statement").
    Also, the debugger highlighted the "in" part of the code.


    I also gave this a shot, and, while VBA accepted this revised code, I still get the "Data Mismatch" error:

    Code:
    'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Phase] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")" Or " WHERE [Zone] in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Data mismatch usually occurs when you have one data type requested in the criteria and another data type in the fields.

    What happens when you build the query in the QBE and run it. Does it work?

  5. #5
    KiEESH is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    4
    Quote Originally Posted by alansidman View Post
    Data mismatch usually occurs when you have one data type requested in the criteria and another data type in the fields.

    What happens when you build the query in the QBE and run it. Does it work?

    Works perfectly in Design View. The query is built using only one, very basic table.

    I also tried changing the data types of each field to match (text), but still no luck.

    Seems like a simple thing to accomplish: run query off of one listbox or the other.

    But, it's just not coming together.


    Thanks very much for offering ideas. Still researching a solution. Hope to hear more suggestions if you have any!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiselect Listbox
    By wwhit in forum Forms
    Replies: 19
    Last Post: 03-09-2015, 02:58 PM
  2. Replies: 8
    Last Post: 09-20-2012, 03:27 PM
  3. Listbox multiselect status
    By Sam23 in forum Programming
    Replies: 5
    Last Post: 03-06-2012, 01:13 PM
  4. multiselect listbox - search result problem
    By svartisya in forum Forms
    Replies: 4
    Last Post: 11-29-2011, 07:17 AM
  5. Append Records using MultiSelect Listbox
    By Ted C in forum Programming
    Replies: 14
    Last Post: 03-15-2011, 01:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums