Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Nz and Dsum

Steel Contributor

Experts,

 

How would I add Nz to the below AmendSum Dsum statement?  I think I need to add Nz if the "Amount" is null.  The amount is NULL in tblFXAmends where IDFXfk]= & [ID] (I did set the default value to 0 but there are a few records that are still NULL)

 

AmendSum: Format(DSum("Amount","tblFXAmends","[IDFXfk]=" & [ID]),"Standard")

 

this is my entire query:

SELECT tblBanks.BankID, tblFX2.Notional, Format(DSum("Amount","tblFXAmends","[IDFXfk]=" & [ID]),"Standard") AS AmendSum, Nz([Notional],0)+(DSum("Amount","tblFXAmends","[IDFXfk]=" & [ID])) AS GTotal
FROM tblFX2 INNER JOIN tblBanks ON tblFX2.Bank = tblBanks.BankID;

 

thank you

2 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution
Just add Nz in the following concept
Nz(Dsum("field","table","Criteria"),0)
Perfect. I thought I tried that but I guess I made a mistake somewhere. anyways, it works. thank you
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution
Just add Nz in the following concept
Nz(Dsum("field","table","Criteria"),0)

View solution in original post