Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help with creating code for IN list (2003)

    I am trying to help create a slightly more user friendy process in Access.

    I have a list of order data where I need to bounce a unique list of tickets against a Oracle table through an ODBC connection. (very large backend)

    Currently if I do a direct join between a table of tickets and this backend table, the query runs and runs and runs as Access tries to pull the Oracle table into memory. (even if I have this field as a KEY when I selected the table through the link process)

    If I code my SQL statement like:
    Select OracleTable.Fieldname, OracleTable.Fieldname2 from OracleTable where OracleTable.Fieldname in ("Value1", "Value2", "Value3");

    the data is returned in a matter of seconds.


    What I would like to do is create a function where I take the list of tickets and create and return a single string that I could then use in the SQL query. (unless someone has a better sugestion)

    The list of tickets normally would be about 10 - 20, so the total length of the string should not be excessive.

    Currently we are exporting the list to a text file, using an editor, creating the "value", list, editing the SQL deleting what was between the ( ) and replacing it with the new string.

    Thanks.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with creating code for IN list (2003)

    You could create a multi select list box, and use this code in the after update event.

    Private Sub lstLocations_AfterUpdate()
    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As QueryDef

    strSQL = "Select OracleTable.Fieldname, OracleTable.Fieldname2 from OracleTable where OracleTable.Fieldname"

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & " & Me.ActiveControl.ItemData(varItem) & ") & " Or "
    Next

    strSQL = strSQL & "IN (" & Left(strTemp, Len(strTemp) - 4) & ")" & ";"


    CurrentDb.QueryDefs("qryYOURQUERY").SQL = strSQL

    End Sub


    Example attached...hope it helps.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with creating code for IN list (2003)

    The keyword IN generally works against a delimited list and expects either commas or semicolons depending on the local settings on your machine. Another slight gotacha is that if the individual values in the list are strings, then each one needs to be delimited as a string, so that you wind up with a list string that looks something like this: <code>"'a', 'b', 'c', 'd'"</code> The SQL string would then wind up looking like this:

    <code>"Select OracleTable.Fieldname, OracleTable.Fieldname2 from OracleTable where OracleTable.Fieldname IN ('a', 'b', 'c', 'd')"</code>

    If you change your code like this:

    <code>For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & "'" & Me.ActiveControl.ItemData(varItem) & "', "
    Next

    strSQL = strSQL & " IN (" & Left(strTemp, Len(strTemp) -2 & ");"</code>

    I think it addresses the requested solution.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with creating code for IN list (2003)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> Thanks Charlotte. I originally had the ' in there for text values in Access, but assumed (I know I know...you know what happens when you assume right?) that the tickets were numbers....the ' would cause an error in access with a number (or am I talking out my <img src=/S/moonlegs.gif border=0 alt=moonlegs width=25 height=29>?)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with creating code for IN list (2003)

    Yes, if the values involved are numeric and not numeric strings, then the ticks need to be left out. I wasn't sure which it was, so I erred on the side of being thorough.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with creating code for IN list (2003)

    Fair 'nuff - thanks.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with creating code for IN list (2003)

    Thanks to both of you for your help. (finally getting back to this)

    After correcting for a few issues and my real table names, this is what I have that is running and updating the query:
    Private Sub List0_AfterUpdate()
    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As QueryDef

    strSQL = "select PUBLIC_SW_CASE.WCTSCTT, PUBLIC_SW_CASE.WCSEVERITY from PUBLIC_SW_CASE where PUBLIC_SW_CASE.WCTSCTT"

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & "'" & Me.ActiveControl.ItemData(varItem) & "', "
    Next

    strSQL = strSQL & " IN (" & Left(strTemp, Len(strTemp) - 2) & ");"

    currentdb.QueryDefs("qry_Output").SQL = strSQL

    End Sub


    One question I have, the multi-select box does let me select more then 1 at a time, but in this database today, I have 182 items to select. I have tried to click on the first item, scroll down and shift-click on the last trying to highlight all records returned, but only those that are clicked on are selected.

    Is there a way to select all records instead of having to click on each 180 records?

    Thanks,
    John
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with creating code for IN list (2003)

    Think I just found my answer, I need to change the select box to a EXTENDED mulit-select instead of a simple.

    Works great!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with creating code for IN list (2003)

    Yep, that's it - Simple lets you select or deselect one item at a time, Extended lets you select items the same way you select files in Windows Explorer:

    Click one item, Shift+Click another one to select a contiguous range. Dragging the mouse does the same.
    Ctrl+Click to select/deselect individual items.

Posting Permissions

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