udate query won't

  • Thread starter Thread starter Rover
  • Start date Start date
R

Rover

I have a select query (qry_subContractReportTotalDue) that sums a field
(see below). I can run that query and it works fine. When I try to use
that total to update the InvAmt field of an existing table, it says
"Enter Parameter Value" "[qry_subContractReportTotalDue]![SumOfAmt]".

What am I doing wrong???


UPDATE tbl_inv_temp SET tbl_inv_temp.InvAmt =
[qry_subContractReportTotalDue]![SumOfAmt];

TIA

Jim

qry_subContractReportTotalDue:

SELECT Sum(Invoice.Amt) AS SumOfAmt
FROM ((Invoice INNER JOIN AppType ON Invoice.AppType = AppType.AppType)
INNER JOIN qry_sum_truck ON Invoice.InvoiceNo = qry_sum_truck.InvoiceNo)
LEFT JOIN tbl_subContract_Inv_billed ON Invoice.InvoiceNo =
tbl_subContract_Inv_billed.InvoiceNo
WHERE (((qry_sum_truck.AppDate) Between #1/1/2004# And #12/31/2004#) AND
((tbl_subContract_Inv_billed.InvoiceNo) Is Null) AND
((Invoice.Void)=False) AND ((Invoice.Owner)="s"));
 
Hi Jim,

I believe you want to use a domain function,
for example:

UPDATE tbl_inv_temp SET tbl_inv_temp.InvAmt =
DMax("SumOfAmt","qry_subContractReportTotalDue");
 
Rover

Are you trying to update the field in the same query that calculates the
total? If so, another approach is to create a second query, based on the
first, to do the updating.

A side note -- it's uncommon to need to store a calculated value, since your
query can generate the total on the fly. You run into synchronization
issues if any of the underlying values get changed.

Can you describe "why" you feel you need to store the calculated value?
 
Back
Top