Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26

    Unanswered: Access form Listbox data to Query

    I have a listbox on a form which contains a list of text strings; I’m trying to use vba to take the selected items and use them as criteria for a query using the ‘LIKE*’ function, to return data with similar text.
    I’m getting an sql syntax error and can’t figure out what’s wrong, something in the sql? Newby to vba / sql and trying to manipulate code already in the db. Any help would be much appreciated. Thanks.

    Private Sub Command2_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strSQL As String
    Dim varIem As Variant
    Dim list1text As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Query2")

    For Each varItem In Me.List0.ItemsSelected
    list1text = list1text & " Or "
    list1text = list1text & "Like" & " '" & Me.List0.ItemData(varItem) & "*' "
    Next varItem
    list1text = Right(list1text, Len(list1text) - 3)

    strSQL = "SELECT * FROM Query1 " & _
    "WHERE Query1.[Vendor] " & list1text & ";"
    qdf.SQL = strSQL
    list1text = ""
    Set db = Nothing
    Set qdf = Nothing
    End Sub

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Darren_Taylor
    I have a listbox on a form which contains a list of text strings; I’m trying to use vba to take the selected items and use them as criteria for a query using the ‘LIKE*’ function, to return data with similar text.
    I’m getting an sql syntax error and can’t figure out what’s wrong, something in the sql? Newby to vba / sql and trying to manipulate code already in the db. Any help would be much appreciated. Thanks.

    Private Sub Command2_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strSQL As String
    Dim varIem As Variant
    Dim list1text As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Query2")

    For Each varItem In Me.List0.ItemsSelected
    list1text = list1text & " Or "
    list1text = list1text & "Like" & " '" & Me.List0.ItemData(varItem) & "*' "
    Next varItem
    list1text = Right(list1text, Len(list1text) - 3)

    strSQL = "SELECT * FROM Query1 " & _
    "WHERE Query1.[Vendor] " & list1text & ";"
    qdf.SQL = strSQL
    list1text = ""
    Set db = Nothing
    Set qdf = Nothing
    End Sub
    Hi, Darren,

    I've made some changes, and bolded them out below. However, I don't understand one line, as I note.

    HTH,
    Sam

    Private Sub Command2_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strSQL As String
    Dim varIem As Variant
    Dim list1text As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Query2")

    list1text =""
    For Each varItem In Me.List0.ItemsSelected
    list1text = list1text & "= '" & Me.List0.ItemData(varItem) & "' Or "
    list1text = list1text & "Query1.[Vendor] Like" & " '" & Me.List0.ItemData(varItem) & "*' "
    Next varItem
    list1text = Right(list1text, Len(list1text) - 3) (What is this line for?)

    strSQL = "SELECT * FROM Query1 " & _
    "WHERE Query1.[Vendor] " & list1text & ";"
    qdf.SQL = strSQL
    list1text = ""
    Set db = Nothing
    Set qdf = Nothing
    End Sub

  3. #3
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Thanks very much for your help! I’m pleased I wasn’t a million miles away. As for that line, can’t honestly say, I was trying to manipulate code from 2 other existing databases and I guess it’s not relevant to this.
    Cheers, much appreciated.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are looping thru your list which suggests that it is multiselect
    ...but your code will only work for a single selection because you are missing an OR

    if it wasn't missing an OR your SQL would WHERE for

    Query1.[Vendor] = '" & Me.List0.ItemData(varItem) & "' OR "
    Query1.[Vendor] Like '" & Me.List0.ItemData(varItem) & "*' OR "
    'that last OR<space> was the missing one
    etc etc etc etc etc for each selected item

    the -3 stuff is to kill the leftover trailing OR<space> after the last listitem is gathered.

    if you don't like trimming the leftover OR, you can use Rudy's magic trick
    WHERE 1=3
    OR Query1.[Vendor] Like '" & Me.List0.ItemData(varItem) & "*' "
    OR Query1.[Vendor] Like '" & Me.List0.ItemData(varItem) & "*' "
    OR Query1.[Vendor] Like '" & Me.List0.ItemData(varItem) & "*' "
    ...the 1=3 never happens so it doesn't get in the way but it does allow you to use leading ORs instead of trailing ORs: no leftovers.

    meanwhile, given that
    LIKE 'Fred*'
    and
    = 'Fred'
    both hit the same record, is it useful to use both LIKE and = ?
    i have no idea if it helps or hinders query execution to include both, but it certainly makes your concatenation longer and messier. it might be worth your while trying a few timing tests to see the impact of including both.

    izy
    Last edited by izyrider; 11-12-05 at 04:47.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    confused myself by mixing your & Sam's code together.
    your original used leading OR<space>
    and then Right() to kill the leading leftover OR
    BTW - use Right$() not Right()
    Sam added the = and lost the OR

    bahhhh!
    it's easier to type code than english.

    here's my version of the truth:

    Code:
    Private Sub Command2_Click()
       Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim varItem As Variant
       Dim strSQL As String
       Dim list1text As String
       Set db = CurrentDb()
       Set qdf = db.QueryDefs("Query2")
       For Each varItem In Me.List0.ItemsSelected
          list1text = list1text & " Or Query1.[Vendor] Like '" & Me.List0.ItemData(varItem) & "*'"
       Next varItem
       strSQL = "SELECT * FROM Query1 WHERE 1=3" & list1text & ";"
       qdf.SQL = strSQL
       qdf.close
       Set qdf = Nothing
       Set db = Nothing
    End Sub
    ...and i dont use .ItemData() since i usually have multicolumn lists
    my loop looks like:

    Code:
    For Each varRow In Me.myList.ItemsSelected
    strSQL = strSQL & " OR fldName LIKE '" & Me.myList.Column(2, varRow) & "*'"
    Next
    ...where in the above example the 2 gets me the value from the third column in the list.


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and a rather delayed-reaction afterthought:

    since this is a list, LIKE is probably inappropriate - after all, the list is an exact representation of the underlying field not just the first few characters (?? isn't it ?).

    if yes - abandon LIKE completely so your loop gets to
    Code:
    For Each varItem In Me.List0.ItemsSelected
       list1text = list1text & " Or Query1.[Vendor] = '" & Me.List0.ItemData(varItem) & "'"
    Next varItem
    and my loop:
    Code:
    For Each varRow In Me.myList.ItemsSelected
       strSQL = strSQL & " OR fldName = '" & Me.myList.Column(2, varRow) & "'"
    Next
    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Cheers izy, much appreciated and thanks for the further inputs, all useful and much needed!
    Thanks

Posting Permissions

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