Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2008
    Posts
    58

    Unanswered: Building SQL criteria string in a Loop expression

    Hello all,

    This question may not be specific to Access necessarily, but since Access has it's own rules for SQL strings I'll post it here. I've got a multiselect list box and I want to build a delete query based on the ItemsSelected collection. Here's the method I have so far:

    Code:
    Dim lst As ListBox
    Dim varItem As Variant
    Dim strCriteria As String
    
    Set lst = Me.MyListBox
    
    If lst.ItemsSelected.Count > 0 Then
         For Each varItem in lst.ItemsSelected
              strCriteria = lst.ItemData(varItem) & " OR " & strCriteria
         Next varItem
    End If
    The bound column of the list box is the PK in the table whose records I wish to delete. The idea of this loop structure is that I end up with a string along the lines of "1 OR 3 OR 5" which I then plug into the Where Condition of a Delete query. My question is how to set up the loop so that it will insert the "OR" in the correct spaces. The current construct always inserts an extra "OR" which therefore causes an error when the Delete query tries to execute. I suppose I need to embed one loop inside another to make this work, but I'll put the matter here in hopes of some feedback. Thanks,

    J

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try rudy's trick:
    Code:
    strCriteria = "1=9" 'this is never going to happen
    If lst.ItemsSelected.Count > 0 Then
         For Each varItem in lst.ItemsSelected
              strCriteria = strCriteria & " OR " & lst.ItemData(varItem) 
         Next varItem
    one alternative (and most frequently used) route is to strip off the trailing " OR " with
    strcriteria = left$(strcriteria, len(strcriteria) - 4)
    but that is boring and slower.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    You could adopt this slightly different syntax in your query:

    SELECT <Records> FROM <Table> WHERE PK IN (1,3,5)

    Then you simply need to assemble a string that will fit between the parentheses. To get around the problem of the trailing comma, simply append an impossible value such as 0 to the list. Of course, if your PK is a text field, you will need to enclose each value in single quotes, but that is only a per value operation.

  4. #4
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by izyrider
    try rudy's trick:
    Code:
    strCriteria = "1=9" 'this is never going to happen
    If lst.ItemsSelected.Count > 0 Then
         For Each varItem in lst.ItemsSelected
              strCriteria = strCriteria & " OR " & lst.ItemData(varItem) 
         Next varItem
    one alternative (and most frequently used) route is to strip off the trailing " OR " with
    strcriteria = left$(strcriteria, len(strcriteria) - 4)
    but that is boring and slower.

    izy
    Thanks izy, but I'm not sure I understand. Won't the bad value with which you initialize strCriteria cause an error in the Delete query? That was my assumption. If that's not the case, then I feel both stupid and frustrated with the inconsistency that is Microsoft Access. Thanks anyway, I'll give it a shot.

    J

  5. #5
    Join Date
    Aug 2008
    Posts
    58
    Well, I added in a dummy value at initialization and it worked fine. Izy, sorry I ever doubted you! Thanks all,

    J

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by JManSF
    Thanks izy, but I'm not sure I understand. Won't the bad value with which you initialize strCriteria cause an error in the Delete query? That was my assumption. If that's not the case, then I feel both stupid and frustrated with the inconsistency that is Microsoft Access. Thanks anyway, I'll give it a shot.

    J
    its not an inconsistency in Access/JET.. its a feature common to all SQL engines.

    the parser dosn't care where the values in the expression come from as long as it can evaluate it to TRUE/FALSE it doesn't care

    eg
    where Manufacturers.ID = Products.ManufacturerID ' you oculd be comparing two values from a table(s),
    where Products.ManufacturerID = 1234 ' you could be using a specified value and a value from a table
    in Access Jet you an request the user enter a parameter
    where Products.ManufacturerID = [Enter reqired manufacturer]

    Using "Rudy's trick" works well and does look a little odd in the SQL, but from a developer perspective looks easier on the eye in code.

    if you have an OR clause using a known FALSE condition as IzyRider suggests, if you are building an and condition use soemthign that evaluates as true eg where 1=1 AND blah......
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by healdem
    if you have an OR clause using a known FALSE condition as IzyRider suggests, if you are building an and condition use soemthign that evaluates as true eg where 1=1 AND blah......
    It seems you've anticipated my current problem. The Delete query is ignoring the AND expression in the following string:
    Code:
    DELETE FROM tblEquipIssues WHERE EquipID = 'ZZ-99' OR 'MUAU-2' AND IssueID = 7;
    'ZZ-99' is the dummy value I added into strCriteria. I'm not sure exactly what the culprit is, but the query is deleting ALL records from tblEquipIssues where the IssueID = 7, not just the ones specified.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not just the ones specified....
    what do you think you specified in
    DELETE FROM tblEquipIssues WHERE EquipID = 'ZZ-99' OR 'MUAU-2' AND IssueID = 7;
    I suspect you meant
    DELETE FROM tblEquipIssues WHERE EquipID = 'ZZ-99' OR ('MUAU-2' AND IssueID = 7);
    OR
    DELETE FROM tblEquipIssues WHERE (EquipID = 'ZZ-99' OR 'MUAU-2') AND IssueID = 7;
    Last edited by healdem; 10-15-08 at 19:25.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2008
    Posts
    58
    [QUOTE=healdem]not just the ones specified....
    what do you think you specified in
    [quote]DELETE FROM tblEquipIssues WHERE EquipID = 'ZZ-99' OR 'MUAU-2' AND IssueID = 7;{/quote]

    I suspect you meant
    [quote]DELETE FROM tblEquipIssues WHERE EquipID = 'ZZ-99' OR ('MUAU-2' AND IssueID = 7);{/quote]

    OR
    DELETE FROM tblEquipIssues WHERE (EquipID = 'ZZ-99' OR 'MUAU-2') AND IssueID = 7;{/quote]
    That's the one. How do you demarcate that in Access SQL?

  10. #10
    Join Date
    Aug 2008
    Posts
    58
    I tried using parentheses but that didn't do the trick either.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so lets take a step backwards
    SQL where clauses are translated into boolean algebra so providing you can identify each question / filter the where clause becomes easier to write

    how about stating in English what it is you want the where clause to return

    eg
    all records whose equipId = "ZZ-99"
    OR
    all records whose Manufacturer is "ACME Mfg" AND Description contains "Roadrunner"

    ..which would equate to

    where equipId = "ZZ-99" OR (Manufacturer = "ACME Mfg" and Description like "%Roadrunner%")
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Aug 2008
    Posts
    58
    All records whose IssueID = 7 AND whose EquipID is in (ZZ-99, etc.). That's the goal. The table I'm deleting from is a union table. Many pieces of Equipment are assigned to an issue. My sub picks up 1 IssueID from the current record, and a collection of EquipIDs from a multi-select list box. There may be a better way to build the EquipID part of the string than what I'm doing now, which is demonstrated a few posts above. Thanks healdem,

    J

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what you are looking for is
    Code:
    DELETE FROM tblEquipIssues
    WHERE (EquipID = 'ZZ-99' OR EquipID = 'MUAU-2') AND IssueID = 7;
    an alternative may be
    Code:
    DELETE FROM tblEquipIssues
    WHERE EquipID in ("ZZ-99", "MUAU-2") AND IssueID = 7;
    SQL is english like, but like all computer languages is very very particular, it doesn't and cannot make assumptions
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Aug 2008
    Posts
    58
    Thanks again healdem. I ended up using your 2nd example. Problem solved. Yet another frustration that could have been (and was, finally) sorted out using the QBE. Bah.

    J

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by JManSF
    Thanks again healdem. I ended up using your 2nd example. Problem solved. Yet another frustration that could have been (and was, finally) sorted out using the QBE. Bah.

    J
    The QBE does a reasonable job of sheltering people from using SQL. but it isn't as flexible and controllable as using SQL itself. it can be a good way of prototyping the query and then tweaking using the SQL view itself

    The QBE doens't expose the full power of SQL, as a result I, and I suspect many of the other contributors here tend not to use it.


    mind you the "in" construct was suggested by Jim Wright a while back
    Last edited by healdem; 10-16-08 at 19:26.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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