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. |
Thread Tools | |
Display Modes | |
|
|