Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Multi Select List Box selecting Query Criteria, Criteria come up with quotation marks


    Hi,
    I am trying to link use a multi select listbox to select records to be displayed in a query and later a report. Currently, the criteria gets to the query, but it comes out as "1" or "2" or "4" (whatever ID numbers I select). It works if I go in after the fact and delete the quotation marks. I am not sure how to remove the quotation marks using my code though. Any help would be greatly appreciated.
    My listbox is called list68. The table that the listbox and query get their data from is Doctors. The query is called Copy. The button causing the action is command6.

    Code:
    Private Sub Command6_Click()
    On Error GoTo Err_Handler
    
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Copy")
    
    
    For Each varItem In Me!List68.ItemsSelected
    strCriteria = strCriteria & "Doctors.ID = " & Chr(34) _
    & Me!List68.ItemData(varItem) & Chr(34) & "Or "
    Next varItem
    
    
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    
    
    strSQL = "SELECT *, [Doctors].[First Name], [Doctors].[Last Name], [Doctors].[Company], [Doctors].[Address], [Doctors].[City], [Doctors].[State], [Doctors].[Zip/Postal Code], [Doctors].[Business Phone] FROM Doctors " & _
    "WHERE " & strCriteria & ";"
    
    
    qdf.SQL = strSQL
    
    
    DoCmd.OpenQuery "Copy"
    
    
    Set db = Nothing
    Set qdf = Nothing
    
    
    Exit_Handler:
    Exit Sub
    
    
    Err_Handler:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Handler
    End Sub
    Thanks again

  2. #2
    Join Date
    Jul 2012
    Posts
    5
    Sorry one last piece. I get an error 3464 Data type mismatch in criteria expression when I run it.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The Chr(34) is the quotes, so take those out (and of course one of the ampersands each).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Join Date
    Jul 2012
    Posts
    5
    Thank you so much. I have been trying it from a bunch of different angles. That was such an easy fix.

  5. #5
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Try -
    For Each varItem In Me!List68.ItemsSelected
    strCriteria = strCriteria & "Doctors.ID = " & Me!List68.ItemData(varItem) & " Or "
    Next varItem

    If your ultimate aim is to display the matching results in report then create the report based on your query showing all results and use your "strCriteria" to filter it.

  6. #6
    Join Date
    Jul 2012
    Posts
    5
    Thanks. To get to the report, I deleted the docmd.openquery line, switched it from the button to after update on the list itself, and replaced the button with an open report button.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-15-2013, 03:36 PM
  2. Multi-Select List Box as Criteria in Query
    By broadwat in forum Queries
    Replies: 6
    Last Post: 09-19-2011, 07:47 AM
  3. Passing Multi Select string to Query criteria
    By oleBucky in forum Queries
    Replies: 4
    Last Post: 05-15-2011, 02:11 PM
  4. Concatenate Quotation Marks
    By millerdav99 in forum Programming
    Replies: 3
    Last Post: 04-11-2011, 09:36 AM
  5. Transferring text within quotation marks
    By Lynette in forum Access
    Replies: 6
    Last Post: 11-24-2010, 11:58 AM

Tags for this Thread

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