Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Search query question

    Hi,

    I have something that I'm trying to accomplish with access but I'm not sure what the easiest way to go about it is.

    I have a table, called ProductFamTbl which has the product name, and family it belongs to. The name is a unique value, but the family is not as it includes various products.
    I have a second table, called AttributesTbl which has detailed information about each of the products.

    I have a form which allows to search by various attributes of the product. However, when using the combo box to specify a family, the query finds no records. All other options and combination work fine. Could anyone please take a look and let me know if they can see any reason why this won't work with the product family?

    My code is below:
    Code:
     strSQL = "SELECT AttributesTbl.Assembly, ImpellerFamilyTbl.FamilyName, ProductNameTbl.ProductName, FrameSizeTbl.FrameSize, " _
        & "TrimTbl.Trim, RotationTbl.Rotation, MaterialTbl.MaterialNmbr, HeatTreatTbl.HeatTreat, AttributesTbl.ScaleFactor, " _
        & "MfgTbl.MfgMethod, AttributesTbl.AddedBy " _
        & "FROM ProductFamilyTbl INNER JOIN (HeatTreatTbl INNER JOIN (MfgTbl INNER JOIN (MaterialTbl INNER JOIN " _
        & "(RotationTbl INNER JOIN (TrimTbl INNER JOIN (FrameSizeTbl INNER JOIN " _
        & "(ProductNameTbl INNER JOIN AttributesTbl ON ProductNameTbl.ProductName = AttributesTbl.ProductName) " _
        & "ON FrameSizeTbl.FrameSize = AttributesTbl.FrameSize) ON TrimTbl.Trim = AttributesTbl.Trim) " _
        & "ON RotationTbl.Rotation = AttributesTbl.Rotation) ON MaterialTbl.MaterialNmbr = AttributesTbl.Material) " _
        & "ON MfgTbl.MfgMethod = AttributesTbl.MfgMethod) ON HeatTreatTbl.HeatTreat = AttributesTbl.HeatTreat) " _
        & "ON ProductFamilyTbl.ProductName = AttributesTbl.ProductName " _
        & "WHERE ((ProductNameTbl.ProductID) =[Forms]![frm_search]![cbo_prod_name]) " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_frame_size]), True, (FrameSizeTbl.FrameID)=[Forms]![frm_search]![cbo_frame_size]) " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_rotation]), True, (RotationTbl.RotationID) = [Forms]![frm_search]![cbo_rotation]) " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_trim]), True, (TrimTbl.TrimID)=[Forms]![frm_search]![cbo_trim])" _
        & "AND iif(isnull([Forms]![frm_search]![cbo_mfg]), True, (MfgTbl.MfgID) = [Forms]![frm_search]![cbo_mfg])" _
        & "AND iif(isnull([Forms]![frm_search]![cbo_family]),True, (ProductFamilyTbl.FamilyName) =[Forms]![frm_search]![cbo_family]);"
    
      Set qdf = db.CreateQueryDef("qry_SearchResults", strSQL)
        For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
        Next prm
    
        Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
        
         If rst.RecordCount >= 1 Then
            DoCmd.OpenQuery "qry_SearchResults", acViewNormal, acReadOnly
         Else
                MsgBox "There are no records that match this criteria"
            
        End If
    Thanks for the help!
    Last edited by nic311; 05-09-12 at 15:16.

  2. #2
    Join Date
    Nov 2011
    Posts
    57
    I realized that there was no iif statement for the Product Name, so I have changed that.

    I now get error 2001: You cancelled the previous operation at the DoCmd.OpenQuery line...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what does your SQL actually look like
    the SQL you are saving to the query object? not the VBA creating the SQL
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2011
    Posts
    57
    The SQL takes inputs from combo boxes to actually create the query object. Every time the search is run, it takes inputs from one or all of the combo boxes (family, product name, frame size, trim, rotation, and manufacturing method). These combo boxes are populated by each of their individual corresponding tables, which are all referenced in the FROM statement.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and your SQL is?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2011
    Posts
    57
    sorry, I guess I'm not sure what you're asking. Could you explain?

    The SQL being used is what is posted above, strSQL. That's the only SQL involved in this.

    Also, I am now getting that error for any scenario of combo boxes being used.

  7. #7
    Join Date
    Nov 2011
    Posts
    57
    I changed all of the [Forms]![frm_search]![cbo_frame_size] to me.cbo_frame_size.value , or whatever the corresponding combo box was. I am now getting a different error:

    Error 3075 Wrong number of arguments used with function in query expression.
    OR A Data mismatch error...

    I have put all the string combo box values in '" & me.cbo_rotation & "'
    and the numerical ones in like "& me.cbo_frame_size &"

    I think this is correct. I can't think of anything else that could be causing me errors. Can anyone help me solve this?

    Thanks

  8. #8
    Join Date
    Nov 2011
    Posts
    57
    This is what my new where clause looks like after changing that out:

    Code:
     & "WHERE iif(isnull('" & Me.cbo_prod_name.Value & "'), True, ImpellerNameTbl.ImpellerID ='" & Me.cbo_prod_name.Value & "') " _
        & "AND iif(isnull('" & Me.cbo_rotation.Value & "'), True, RotationTbl.RotationID = '" & Me.cbo_rotation.Value & "') " _
        & "AND iif(isnull(" & Me.cbo_trim.Value & "), True, TrimTbl.TrimID=" & Me.cbo_trim.Value & ")" _
        & "AND iif(isnull(" & Me.cbo_frame_size.Value & "), True, FrameSizeTbl.FrameID=" & Me.cbo_frame_size.Value & ") " _
        & "AND iif(isnull('" & Me.cbo_mfg.Value & "'), True, MfgTbl.MfgID = '" & Me.cbo_mfg.Value & "')" _
         & "AND iif(isnull(" & Me.cbo_family.Value & "),True, (ProductFamilyTbl.FamilyName) = '" & Me.cbo_family.Value & "');"
    I changed this because when I tried creating this query out of Access, it said something about it being too complex to evaluate and to try to put in variables for it, so I thought this may be the issue.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what does your SQL actually look like?
    NOT the VBA code that creates the SQL but the SQL itself that is causing the problem.

    put a watch / breakpoint on the code then examine the value of strSQL, then copy and paste it here
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2011
    Posts
    57
    Oh I understand now! Here it is:

    Code:
    SELECT AttributesTbl.Assembly, ProductNameTbl.ProductName, FrameSizeTbl.FrameSize, TrimTbl.Trim, RotationTbl.Rotation, MaterialTbl.MaterialNmbr, HeatTreatTbl.HeatTreat, AttributesTbl.ScaleFactor, MfgTbl.MfgMethod, AttributesTbl.AddedBy FROM ProductFamilyTbl INNER JOIN (HeatTreatTbl INNER JOIN (MfgTbl INNER JOIN (MaterialTbl INNER JOIN (RotationTbl INNER JOIN (TrimTbl INNER JOIN (FrameSizeTbl INNER JOIN (ProductNameTbl INNER JOIN AttributesTbl ON ProductNameTbl.ProductName = AttributesTbl.ProductName) ON FrameSizeTbl.FrameSize = AttributesTbl.FrameSize) ON TrimTbl.Trim = AttributesTbl.Trim) ON RotationTbl.Rotation = AttributesTbl.Rotation) ON MaterialTbl.MaterialNmbr = AttributesTbl.Material) ON MfgTbl.MfgMethod = AttributesTbl.MfgMe
    thod) ON HeatTreatTbl.HeatTreat = AttributesTbl.HeatTreat) ON ProductFamilyTbl.ProductName = AttributesTbl.ProductName WHERE iif(isnull('1653'), True, ProductFamilyTbl.FamilyName = '1653') AND iif(isnull(), True, ProductNameTbl.ProductID = ) AND iif(isnull(), True, RotationTbl.RotationID = ) AND iif(isnull(), True, TrimTbl.TrimID = ) AND iif(isnull(), True, FrameSizeTbl.FrameID = ) AND iif(isnull(''), True, MfgTbl.MfgMethod = '');
    The iif clause is supposed to return all values if the combo box is null. If all of the combo boxes are given values, it works just fine. But now if they are not it gives me the following error:

    Runtime error 3075: Extra ) in query expression
    It references the entire where clause after that.

  11. #11
    Join Date
    Nov 2011
    Posts
    57
    I should add that that the above was a trial of only having the product family combo box filled in. When they are all filled in, it looks like this:

    Code:
    SELECT AttributesTbl.Assembly, ProductTbl.ProductName, FrameSizeTbl.FrameSize, TrimTbl.Trim, RotationTbl.Rotation, MaterialTbl.MaterialNmbr, HeatTreatTbl.HeatTreat, AttributesTbl.ScaleFactor, MfgTbl.MfgMethod, AttributesTbl.AddedBy
     FROM ProductFamilyTbl INNER JOIN (HeatTreatTbl INNER JOIN (MfgTbl INNER JOIN (MaterialTbl INNER JOIN (RotationTbl INNER JOIN (TrimTbl INNER JOIN (FrameSizeTbl INNER JOIN (ProductNameTbl INNER JOIN AttributesTbl ON ProductNameTbl.ProductName = AttributesTbl.ProductName) ON FrameSizeTbl.FrameSize = AttributesTbl.FrameSize) ON TrimTbl.Trim = AttributesTbl.Trim) ON RotationTbl.Rotation = AttributesTbl.Rotation) ON MaterialTbl.MaterialNmbr = AttributesTbl.Material) ON MfgTbl.MfgMethod = AttributesTbl.MfgMe
    thod) ON HeatTreatTbl.HeatTreat = AttributesTbl.HeatTreat) ON ProductFamilyTbl.ProductName = AttributesTbl.ProductName 
    WHERE iif(isnull('466'), True, ProductFamilyTbl.FamilyName = '466') AND iif(isnull(53), True, ProductNameTbl.ProductID = 53) AND iif(isnull(1), True, RotationTbl.RotationID = 1) AND iif(isnull(4), True, TrimTbl.TrimID = 4) AND iif(isnull(3), True, FrameSizeTbl.FrameID = 3) AND iif(isnull('EB'), True, MfgTbl.MfgMethod = 'EB');
    In this instance, it works just fine.
    Last edited by nic311; 05-09-12 at 16:52.

  12. #12
    Join Date
    Nov 2011
    Posts
    57

    Multiple iif (not nested!) joined by AND cause query to find no records

    Hi,

    I currently have a form with 6 combo boxes. I am creating a query on the fly using a sql string in Access VBA. I want to have it so that if the user leaves one or multiple of the combo boxes empty, the query will return all values associated with the field that that combo box is attached to. My code is below:

    Code:
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("AttributesTbl", dbOpenDynaset)
    
    On Error Resume Next
     db.QueryDefs.Delete "qry_impellerSearchResults"
    On Error GoTo 0
    
    strSQL = "SELECT AttributesTbl.Assembly, ProductNameTbl.ProductName, FrameSizeTbl.FrameSize, " _
        & "TrimTbl.Trim, RotationTbl.Rotation, MaterialTbl.MaterialNmbr, HeatTreatTbl.HeatTreat, AttributesTbl.ScaleFactor, " _
        & "MfgTbl.MfgMethod, AttributesTbl.AddedBy " _
    & "FROM HeatTreatTbl INNER JOIN (MfgTbl INNER JOIN (MaterialTbl INNER JOIN " _
        & "(RotationTbl INNER JOIN (TrimTbl INNER JOIN (FrameSizeTbl INNER JOIN " _
        & "(ProductNameTbl INNER JOIN AttributesTbl ON ProductNameTbl.ProductName = AttributesTbl.ProductName) " _
        & "ON FrameSizeTbl.FrameSize = AttributesTbl.FrameSize) ON TrimTbl.Trim = AttributesTbl.Trim) " _
        & "ON RotationTbl.Rotation = AttributesTbl.Rotation) ON MaterialTbl.MaterialNmbr = AttributesTbl.Material) " _
        & "ON MfgTbl.MfgMethod = AttributesTbl.MfgMethod) ON HeatTreatTbl.HeatTreat = AttributesTbl.HeatTreat " _
     & "WHERE iif(isnull([Forms]![frm_search]![cbo_prod_name]), True, ProductNameTbl.ProductName = [Forms]![frm_search]![cbo_prod_name]) " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_rotation]), True, RotationTbl.RotationID =[Forms]![frm_search]![cbo_rotation])" _
        & "AND iif(isnull([Forms]![frm_search]![cbo_trim]), True, TrimTbl.TrimID = [Forms]![frm_search]![cbo_trim]) " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_frame_size]), True, FrameSizeTbl.FrameID = [Forms]![frm_search]![cbo_frame_size]); " _
        & "AND iif(isnull([Forms]![frm_search]![cbo_mfg]), True, MfgTbl.MfgMethod = [Forms]![frm_search]![cbo_mfg]);"
    
    Set qdf = db.CreateQueryDef("qry_impellerSearchResults", strSQL)
        For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
        Next prm
        Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
        
         If rst.RecordCount >= 1 Then
            DoCmd.OpenQuery "qry_impellerSearchResults", acViewNormal, acReadOnly
         Else
                MsgBox "There are no records that match this criteria"
            
        End If
        
        Set qdf = Nothing
       
    
    rst.Close
    db.Close
    If I use just one of the iif statements, it works as it is supposed to. If the combo box is empty, it returns all values, otherwise it returns whatever the combo box input is. However, with more than one of these statements, it doesn't always work. If all the combo boxes are filled out OR all are null, then the query runs fine. However, if some are filled out and some are not, it stops working and rst.recordcount = 0. Does anyone have any insight as to what the issue may be, or what another way of accomplishing this same task is?

    Thanks
    Last edited by nic311; 05-10-12 at 09:19.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nic311 View Post
    Oh I understand now! Here it is:

    Code:
    SELECT AttributesTbl.Assembly, ProductNameTbl.ProductName, FrameSizeTbl.FrameSize, TrimTbl.Trim, RotationTbl.Rotation, MaterialTbl.MaterialNmbr, HeatTreatTbl.HeatTreat, AttributesTbl.ScaleFactor, MfgTbl.MfgMethod, AttributesTbl.AddedBy FROM ProductFamilyTbl INNER JOIN (HeatTreatTbl INNER JOIN (MfgTbl INNER JOIN (MaterialTbl INNER JOIN (RotationTbl INNER JOIN (TrimTbl INNER JOIN (FrameSizeTbl INNER JOIN (ProductNameTbl INNER JOIN AttributesTbl ON ProductNameTbl.ProductName = AttributesTbl.ProductName) ON FrameSizeTbl.FrameSize = AttributesTbl.FrameSize) ON TrimTbl.Trim = AttributesTbl.Trim) ON RotationTbl.Rotation = AttributesTbl.Rotation) ON MaterialTbl.MaterialNmbr = AttributesTbl.Material) ON MfgTbl.MfgMethod = AttributesTbl.MfgMe
    thod) ON HeatTreatTbl.HeatTreat = AttributesTbl.HeatTreat) ON ProductFamilyTbl.ProductName = AttributesTbl.ProductName WHERE iif(isnull('1653'), True, ProductFamilyTbl.FamilyName = '1653') AND iif(isnull(), True, ProductNameTbl.ProductID = ) AND iif(isnull(), True, RotationTbl.RotationID = ) AND iif(isnull(), True, TrimTbl.TrimID = ) AND iif(isnull(), True, FrameSizeTbl.FrameID = ) AND iif(isnull(''), True, MfgTbl.MfgMethod = '');
    The iif clause is supposed to return all values if the combo box is null. If all of the combo boxes are given values, it works just fine. But now if they are not it gives me the following error:

    Runtime error 3075: Extra ) in query expression
    It references the entire where clause after that.
    look suspicious to me
    if isnull('') likewise
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Nov 2011
    Posts
    57
    I tried changing the WHERE clause to the following:


    Code:
    "WHERE ProductNameTbl.ProductID = [Forms]![frm_search]![cbo_prod_name] OR [Forms]![frm_search]![cbo_prod_name] IS NULL " _
        & "AND RotationTbl.RotationID = [Forms]![frm_search]![cbo_rotation] OR [Forms]![frm_search]![cbo_rotation] IS NULL " _
        & "AND TrimTbl.TrimID = [Forms]![frm_search]![cbo_trim] OR [Forms]![frm_search]![cbo_trim] IS NULL " _
        & "AND FrameSizeTbl.FrameID = [Forms]![frm_search]![cbo_frame_size] OR [Forms]![frm_search]![cbo_frame_size] IS NULL " _
        & "AND MfgTbl.MfgMethod = [Forms]![frm_search]![cbo_mfg] OR [Forms]![frm_search]![cbo_mfg] IS NULL;"

    This also doesn't work... I'm out of ideas...

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where are you using this?
    you may be better off building the SQL ont he fly, omitting any combo boxes that aren't populated in yoiur where clause

    either set the forms record source to that or save the query to the querydefs collection.
    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
  •