update query returning "null"

[discover142]

i have two update query that runs dynamically.
Query1 -> Query2.

Query1 gathers sum amounts from other tables.
tbl_master.CashedAmt = Dsum(value1) + Dsum(value2) etc.
where both values are from different table.

tbl_master.Original amount is recorded by user.

Query2, is a simple update query
[OriginalAmt] - [CashedAmt]

I think Query2 is working fine but because Query1 is returning blank values when there's no value to collect from other tables. I've tried setting default value of CashedAmt to "0" but still no luck..

I think if i can get it to return 0 instead of null will fix this problem...
any idea how i can fix this problem?

 

[lespaul]

what's the SQL for the two queries?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[discover142]

Query1.
UPDATE tbl_BudMaster SET tbl_BudMaster.ActAmt = DSum("budgetamt","tbl_Vregister","budgetkey = '" & budgetkey & "'")+DSum("BalanceAmt","tbl_PSC","budgetkey='" & budgetkey & "'")+DSum("tranxAmt","tbl_DeptActTxDetail","budgetkey = '" & budgetkey & "'");

Query2.
UPDATE tbl_BudMaster SET tbl_BudMaster.BalanceAmt = [budgetamt]-[actamt];

 

[discover142]

any idea?

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886