Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Is there an easy way of doing this?

    Hi,
    I have a form where I want to place around 10 combo boxes on that will enable the user to filter the records he sees. Easy enough to do, BUT is there an easy way in which I can use these combo's in an AND/OR situation. ie if the user selects "Combo 1" and wants to see records that match this AND "Combo2" AND "Combo3" OR "Combo 7". What I've added to each Combo is an AND/OR toggle but I don't know if this can be incorporated in 1 query Or do I have to create a query for each possible combination?
    My thinking is that you could build and SQL script programatically but I have a clue how

    Regards
    John

  2. #2
    Join Date
    Apr 2004
    Posts
    1

    Building a SQL String

    This probably isn't much help as it demonstrate a "brute force" approach. I'm sure there are more "elegant" solutions but this worked for me.

    I had two list boxes with a drop down in between where I could select "and" or "or". I could select multiple items in the first and/or second list boxes which would be used in the Where clause of my SQL statement.

    The following routine in the click event of a button would build the SQL string based on selections in the two list boxes with an "and" or "or" combo box in between.

    Basically you start with the main part of your SQL string, then you began adding "where" clauses to the end of the string logically based on what was selected in the list boxes or combo boxes.

    I probably should have used a case select for this, otherwise the logic can get very complicated as the more controls you have on the form the different combinations you have to check for becomes ridiculous quickly.


    Dim frm As Form, ctl As Control
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim ctlProduct As Control
    'Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strWhereSQL As String
    Dim strBooLogic As String
    Set frm = Forms!frmplanning
    Set ctl = frm!MultiSelectClass
    Set ctlProduct = frm!MultiSelectProductCode
    Set db = CurrentDb
    strBooLogic = cmbAndOr

    'This is the basic query sql string which is the starting point

    strSQL1 = "SELECT qryItems.ItemID, qryItems.Description, qryItems.QOH, qryItems.Type, qryItems.FillSize, tblPlanning.ToBeFilled, qryItems.ItemClass, qryItems.ProductLine, IIf(IsNull([reorderqty]),0,[reorderqty]) AS ReOrderQty1, TempDemand.SumOfInvoiceLineQuantity, TempDemand.SumOfInvoiceLineQuantity AS Demand, [qoh]+nz([tobefilled])-[demand] AS Available, IIf([reorderqty1]>0,[available]/[reorderqty1],0) AS [Percent], IIf(Round([tempsuggested]/[fillfactor],0)=0 And [tempsuggested]>0,1,Round([tempsuggested]/[fillfactor])) AS [Suggested#Drums], [goalpercent]*[reorderqty]-[qoh]+[demand] AS NewSuggested, IIf([reorderqty1]=0,0,IIf([percent]<[lowthreshold],[reorderqty]*[goalpercent]-[qoh]+[demand],0)) AS TempSuggested, 7040/[fillsize] AS FillFactor, ([Suggested#Drums])*[fillfactor] AS Suggested, tblPlanning.PriorityPercent, qryItems.LowThreshold, qryItems.HighThreshold, qryItems.GoalPercent" _
    & " FROM (qryItems LEFT JOIN tblPlanning ON qryItems.ItemID = tblPlanning.ItemID) LEFT JOIN TempDemand ON qryItems.ItemID = TempDemand.ItemID" _
    & " WHERE (((qryItems.Type)='inv')) "
    strSQL1 = "SELECT qryItems.ItemID, qryItems.Description, qryItems.QOH, qryItems.Type, qryTheListToBeFilled.TheListToBeFilled, qryTheListToBeFilled.Made, qryItems.FillSize, tblPlanning.ToBeFilled, qryItems.ItemClass, qryItems.ProductLine, IIf(IsNull([reorderqty]),0,[reorderqty]) AS "
    strSQL1 = strSQL1 & "ReOrderQty1, TempDemand.SumOfInvoiceLineQuantity, IIf(IsNull([SumOfInvoiceLineQuantity]),0,[sumofinvoicelinequantity]) AS Demand, IIf([made]=Yes,[qoh]+nz([tobefilled])-[demand]+[thelisttobefilled],[qoh]+nz([tobefilled])-[demand]) AS Available, IIf([reorderqty1]>0,[available]/[reorderqty1],IIf([demand]>0,[available]/[demand],0)) AS [Percent], IIf(Round([tempsuggested]/[fillfactor],0)=0 And [tempsuggested]>0,1,Round([tempsuggested]/[fillfactor])) AS [Suggested#Drums], [goalpercent]*[reorderqty]-[qoh]+[demand] AS NewSuggested, IIf([percent]<[lowthreshold],[reorderqty]*[goalpercent]-[qoh]+[demand],0) AS TempSuggested, 6400/[fillsize] AS FillFactor, IIf([reorderqty1]=0 And [demand]=0,0,[Suggested#Drums]*[fillfactor]) AS Suggested, tblPlanning.PriorityPercent, "

    strSQL1 = strSQL1 & "qryItems.LowThreshold, qryItems.HighThreshold, qryItems.GoalPercent"

    strSQL1 = strSQL1 & " FROM ((qryItems LEFT JOIN tblPlanning ON qryItems.ItemID=tblPlanning.ItemID) LEFT JOIN TempDemand ON qryItems.ItemID=TempDemand.itemid) LEFT JOIN qryTheListToBeFilled ON tblPlanning.ItemID=qryTheListToBeFilled.ItemID"

    strSQL1 = strSQL1 & " WHERE (((qryItems.Type)='inv')) "

    'This is the end of the basic SQL string, which I built in Query builder and copy/pasted into this sub

    'Check to see if anything was selected in 1st listbox

    If ctlProduct.ItemsSelected.Count > 0 Then
    strWhereSQL = "AND [productline]='"
    For Each varItem In ctlProduct.ItemsSelected
    strWhereSQL = strWhereSQL & ctlProduct.ItemData(varItem) & "' OR [productline]='"
    Next varItem
    strWhereSQL = strWhereSQL & "' "
    strSQL2 = strSQL1 & Left$(strWhereSQL, Len(strWhereSQL) - 20)
    End If

    'Check to see if anything was selected in the 2nd listbox

    If ctl.ItemsSelected.Count > 0 Then

    If ctlProduct.ItemsSelected.Count > 0 Then
    strWhereSQL = "AND ([productline]='"
    For Each varItem In ctlProduct.ItemsSelected
    strWhereSQL = strWhereSQL & ctlProduct.ItemData(varItem) & "' OR [productline]='"
    Next varItem
    strWhereSQL = strWhereSQL & "' "
    strSQL2 = strSQL1 & Left$(strWhereSQL, Len(strWhereSQL) - 20)
    End If

    If ctlProduct.ItemsSelected.Count = 0 Then
    strWhereSQL = "AND [itemclass]='"
    Else
    strWhereSQL = strBooLogic & " [itemclass]='"
    End If
    For Each varItem In ctl.ItemsSelected
    strWhereSQL = strWhereSQL & ctl.ItemData(varItem) & "' OR [itemclass]='"
    Next varItem
    strWhereSQL = strWhereSQL & "'"

    If ctlProduct.ItemsSelected.Count > 0 Then
    strSQL = strSQL2 & Left$(strWhereSQL, Len(strWhereSQL) - 18)
    strSQL = strSQL & ")"
    End If
    If ctlProduct.ItemsSelected.Count = 0 Then
    strSQL = strSQL1 & Left$(strWhereSQL, Len(strWhereSQL) - 18)
    End If
    End If

    'If nothing is selected in either list box then simply set the recordsource of the subform (where the result will display) equal to a query

    If ctlProduct.ItemsSelected.Count = 0 And ctl.ItemsSelected.Count = 0 Then
    Me.sfrmPlanning.Form.RecordSource = ("qryplanningnew")
    Exit Sub
    End If

    If ctlProduct.ItemsSelected.Count > 0 And ctl.ItemsSelected.Count = 0 Then
    strSQL = strSQL2
    End If

    'Set the recordsource of the subform equal to the finished string
    Me.sfrmPlanning.Form.RecordSource = strSQL

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Quote Originally Posted by Sticker
    Hi,
    I have a form where I want to place around 10 combo boxes on that will enable the user to filter the records he sees. Easy enough to do, BUT is there an easy way in which I can use these combo's in an AND/OR situation. ie if the user selects "Combo 1" and wants to see records that match this AND "Combo2" AND "Combo3" OR "Combo 7". What I've added to each Combo is an AND/OR toggle but I don't know if this can be incorporated in 1 query Or do I have to create a query for each possible combination?
    My thinking is that you could build and SQL script programatically but I have a clue how

    Regards
    John
    John,

    I think you are in a quandry already even before you explore possible solutions. Where do the parentheses go? Is that (Combo1 AND Combo2 AND Combo3) OR Combo7 or is it Combo1 AND Combo2 AND (Combo3 OR Combo7) and so on?

    But yes, it is possible to programmatically build an SQL string with the WHERE clause constructed according to control properties. No, you don't need a string for every possibility.
    Rod

    fe_rod@hotmail.com

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks guy's for your replies.

    I see I'm going to get myself into a right 2 and 8 over this, Rod I see your point about the parentheses, not something that I had taken into consideration

    Regards
    John

Posting Permissions

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