update query not updating correctly (2nd try)

[cys813]

I have an update query which updates a master table based on a sub table.

here's the SQL of the update query:

UPDATE tbl_ContractLedger AS C INNER JOIN tbl_CapitalPrs AS P ON C.ContractID = P.ContractID SET C.PRamt = NZ(DSum("Amount","tbl_CapitalPrs","ContractID='" & [C].[ContractID] & "'"),0)
WHERE (((P.[ApprovalCode])="n"));

I thought it was updating fine but then when I erased the data on Capital PR, it says 0 records updated.

For example(tbl_P), contractID=1 | $50
contractID=1 | $50

on tbl_C it should show $100 where contractID=1.
at first it seemed like it's working but when i erased both of those entry (i was expecting $0) i still have $100.

Since it's a DSUM shouldn't it update it to $0?

I know im probably not making much sense but I really can't get this to work. any suggestions? thx in advance

ps. yes, i do know that Storing a calculated field breaks the rules of databases: Fundamentals of Relational Database Design

 

[dhookom]

Is this what you are attempting:

UPDATE tbl_ContractLedger SET PRamt = NZ(DSum("Amount","tbl_CapitalPrs","[ApprovalCode])='n' AND ContractID='" & [ContractID] & "'"),0);

Duane
Hook'D on Access
MS Access MVP

 

[cys813]

i get error ".. due to a type conversion failure"

thx for the replay btw

 

[dhookom]

Based on the error message I expect ContractID is actually numeric so the single quotes need to be removed.

UPDATE tbl_ContractLedger SET PRamt = NZ(DSum("Amount","tbl_CapitalPrs","[ApprovalCode])='n' AND ContractID=" & [ContractID]),0);

Duane
Hook'D on Access
MS Access MVP

 

[cys813]

actually the "amount" and "PRamt"is currency, "contractID" is text in both table. I am still getting the type conversion error. is there anything more i can try? I've been recently getting lot of this error using vista. could that be why?

 

[Remou]

What about:

UPDATE tbl_ContractLedger SET tbl_ContractLedger.PRamt = DSum("Amount","tbl_CapitalPrs","[ApprovalCode]='n' AND ContractID='" & [ContractID] & "'");

 

[dhookom]

Open the debug window (press Ctrl+G) and enter

? DSum("Amount","tbl_CapitalPrs","[ApprovalCode]='n' AND ContractID='1'")

Do you get the expected result?

Duane
Hook'D on Access
MS Access MVP