If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
aggregate calculation works in select query but not an update quer
The below query works well as a Select Query. But when I turn it into an
update query, I get a whole lot of nothing in the results. Interestingly, if I try to call this query from a Macro on my form, I get error (-20324) repeated the number of times that there are records in the query. It was designed to go into a table of price breaks & decide whether the quantities being ordered meet the price breaks. It then takes either that adjusted price, or the original price & populate the values into a table. Select Query: SELECT QryBOMDetails2.ParentBOMID, QryBOMDetails2.BOMPrice, IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) AS q FROM QryBOMDetails2 WHERE (((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]) AND ((QryBOMDetails2.BOMPrice) Is Null)); Update Query: UPDATE QryBOMDetails2 SET QryBOMDetails2.BOMPrice = IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) WHERE (((QryBOMDetails2.BOMPrice) Is Null) AND ((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID])); FYI I'm doing this using the query builder. Thanks in advance for any advice. |
#2
|
|||
|
|||
aggregate calculation works in select query but not an update quer
QryBOMDetails2
We probably need to see the sql for it. There's many reasons why a query isn't updateable. BTW: Check out my signature line! -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "bicyclops" wrote: The below query works well as a Select Query. But when I turn it into an update query, I get a whole lot of nothing in the results. Interestingly, if I try to call this query from a Macro on my form, I get error (-20324) repeated the number of times that there are records in the query. It was designed to go into a table of price breaks & decide whether the quantities being ordered meet the price breaks. It then takes either that adjusted price, or the original price & populate the values into a table. Select Query: SELECT QryBOMDetails2.ParentBOMID, QryBOMDetails2.BOMPrice, IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) AS q FROM QryBOMDetails2 WHERE (((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]) AND ((QryBOMDetails2.BOMPrice) Is Null)); Update Query: UPDATE QryBOMDetails2 SET QryBOMDetails2.BOMPrice = IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) WHERE (((QryBOMDetails2.BOMPrice) Is Null) AND ((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID])); FYI I'm doing this using the query builder. Thanks in advance for any advice. |
#3
|
|||
|
|||
aggregate calculation works in select query but not an update
QryBOMDetails2 is a biggie. But it's also attached to a form & normally
updates with no problem. Sql is below. SELECT TblBOMDetails.LineItem, TblBOMDetails.BOMDetailID, TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID, [PartPrefix] & "-" & Format([Part#Suffix],"0000") AS [ItemPart#], TblBOMDetails.ParentBOMID, TblBOMDetails.Qty, TblBOMDetails.OverBuyQty, TblBOMDetails.Refdes, TblComps.Description, TblPartPrefix.[Prefix Description], TblBOMDetails.MfrPNID, TblMFRPN.MFRPNPart, TblMFR.MFRMfrName, TblMFRPN.MFRDesc, TblMFRPN.Price AS MfrBasePrice, TblBOMDetails.VendorPNID, TblVendorPN.VendorID, TblVendor.SUSupplier, TblVendorPN.VendorPN, TblBOMDetails.CustPNID, TblCompanies.CompanyName, TblCustPN.CustPN, TblBOMDetails.SourceFromVendor, TblBOMDetails.VendorSourceQty, TblBOMDetails.SourceFromCust, TblBOMDetails.CustSourceQty, TblBOMDetails.SourceFromMfr, TblBOMDetails.MfrSourceQty, TblBOMDetails.SourceFromStock, TblBOMDetails.StockSourceQty, TblComps.StockQty, TblBOM.BuildQty, (Nz([BuildQty],1)*[Qty])+(Nz([OverBuyQty],0)) AS ExtQty, TblComps.UnitsID, TblUnits.BuyAs, TblBOMDetails.Price AS BOMPrice, TblBOMDetails.MfrPNTemp, TblVendor.SUID, IIf([ExtQty](Nz([VendorSourceQty],0))+(Nz([CustSourceQty],0))+(Nz([MfrSourceQty],0))+(Nz([StockSourceQty],0)),1,0) AS QtyMismatchFlag FROM TblVendor RIGHT JOIN (TblVendorPN RIGHT JOIN (TblUnits RIGHT JOIN (TblPartPrefix RIGHT JOIN (TblMFR RIGHT JOIN ((TblCompanies RIGHT JOIN TblCustPN ON TblCompanies.CompanyID = TblCustPN.CustID) RIGHT JOIN (TblBOM RIGHT JOIN ((TblComps RIGHT JOIN TblBOMDetails ON TblComps.[Part#ID] = TblBOMDetails.PartNumID) LEFT JOIN TblMFRPN ON TblBOMDetails.MfrPNID = TblMFRPN.MFRPNID) ON TblBOM.BOMID = TblBOMDetails.ParentBOMID) ON TblCustPN.CustPNID = TblBOMDetails.CustPNID) ON TblMFR.MFRID = TblMFRPN.MFRPNMFRID) ON TblPartPrefix.PartPrefixID = TblComps.PartPrefixID) ON TblUnits.ID = TblComps.UnitsID) ON TblVendorPN.VendorPNID = TblBOMDetails.VendorPNID) ON TblVendor.SUID = TblVendorPN.VendorID ORDER BY TblBOMDetails.LineItem; "Jerry Whittle" wrote: QryBOMDetails2 We probably need to see the sql for it. There's many reasons why a query isn't updateable. BTW: Check out my signature line! -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "bicyclops" wrote: The below query works well as a Select Query. But when I turn it into an update query, I get a whole lot of nothing in the results. Interestingly, if I try to call this query from a Macro on my form, I get error (-20324) repeated the number of times that there are records in the query. It was designed to go into a table of price breaks & decide whether the quantities being ordered meet the price breaks. It then takes either that adjusted price, or the original price & populate the values into a table. Select Query: SELECT QryBOMDetails2.ParentBOMID, QryBOMDetails2.BOMPrice, IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) AS q FROM QryBOMDetails2 WHERE (((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]) AND ((QryBOMDetails2.BOMPrice) Is Null)); Update Query: UPDATE QryBOMDetails2 SET QryBOMDetails2.BOMPrice = IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And [Level]=" & [ExtQty])) WHERE (((QryBOMDetails2.BOMPrice) Is Null) AND ((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID])); FYI I'm doing this using the query builder. Thanks in advance for any advice. |
Thread Tools | |
Display Modes | |
|
|