Prev: Shift several columns in table
Next: Retrieve all record if check box = 0 (Not checked)
From: bicyclops on 27 May 2010 15:53
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.


From: Jerry Whittle on 27 May 2010 16:56
> 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.
>
>
 | 
Pages: 1
Prev: Shift several columns in table
Next: Retrieve all record if check box = 0 (Not checked)