Results 1 to 4 of 4
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    List Box Items as criteria

    I am using the following code to generate a Report based on the multi items selected in a List Box:

    Private Sub Command12_Click()
    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("QryListLots")
    For Each varItem In Me!ListLots.ItemsSelected
    strCriteria = strCriteria & "," & Me!ListLots.ItemData(varItem) & ""
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT * FROM [tblLots] " & _
    "WHERE [tblLots].[LotID] IN(" & strCriteria & ");"
    qdf.SQL = strSQL


    DoCmd.OpenReport "PressWt-DeliveryWt", acViewPreview
    Set db = Nothing
    Set qdf = Nothing
    End Sub

    In the above code [LotID] is a Long Integer & it works fine.
    What I want to know is if [LotID] is a string how the strCriteria quotes should be.

    Thanks
    Alex

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Add single quote around the criteria to delimit them as a string ;

    Code:
    strCriteria = strCriteria & ",'" & Me!ListLots.ItemData(varItem) & "'"
    You might need to experiment with your starting string.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Or dbl-click the items in the list, which runs an append query to a picked table.

    the result query joins the picked table with the main data table.
    no code needed.

  4. #4
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks Minty, your solution worked.

    Alex

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

Similar Threads

  1. Replies: 2
    Last Post: 05-24-2016, 08:01 AM
  2. Replies: 5
    Last Post: 08-11-2014, 03:08 PM
  3. Replies: 3
    Last Post: 05-24-2014, 03:56 PM
  4. Items in a List Box
    By looloo in forum Queries
    Replies: 3
    Last Post: 09-23-2011, 11:36 PM
  5. list box items
    By thewabit in forum Forms
    Replies: 12
    Last Post: 01-01-2010, 08:59 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