none
DSum expression RRS feed

  • Question

  • I have a table for bankpayments, and one for creditor invoices. My Dsum expression is. DSum("[icsexcvat]","q06InvCrdsub","[Cmbent_Id]=" & [CmbentID])+DSum("[bpsexclvat]","q06BnkPmtSub","[CmbEntID]=" & [VehID]).

    When there is an amount in each of the two fields, i.e. icsexcvat and bpsexclvat it calculates like I want it to. When either of the queries does not have a transaction it shows a "null" value. Even when I add Nz in front of the Dsum it returns a zero while it shouldnt when only one table has a transaction with the applicable reference.

    My business buy cars. We may buy the car on credit but buy additions cash. As I said when both tables have at least one transaction the expression returns correct. When either of the two tables have no transaction against the same car it gives "null" or Zero. But one table have an amount. What should I do to calculate the one amount plus no transaction in the other table?

    Thank you.

    Friday, March 25, 2016 5:20 AM

Answers

  • Have you tried using Nz on both expressions?

    Val(Nz(DSum("[icsexcvat]","q06InvCrdsub","[Cmbent_Id]=" & [CmbentID]),0))+Val(Nz(DSum("[bpsexclvat]","q06BnkPmtSub","[CmbEntID]=" & [VehID]),0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, March 25, 2016 10:09 AM
  • Do the fields balance and LdgAccNo occur in the Union001 query?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 30, 2016 4:55 PM

All replies

  • Have you tried using Nz on both expressions?

    Val(Nz(DSum("[icsexcvat]","q06InvCrdsub","[Cmbent_Id]=" & [CmbentID]),0))+Val(Nz(DSum("[bpsexclvat]","q06BnkPmtSub","[CmbEntID]=" & [VehID]),0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, March 25, 2016 10:09 AM
  • Beeeeuuuatiful! It worked. Thanks. Hans.

    From Hans

    Friday, March 25, 2016 10:43 AM
  • I have a Union query 6500 records so far. Need to get a report from that. Tried expressions in a query.   Balance: +DSum("[balance]","Union001","[LdgAccNo]=" & [LdgAccNo])  and     Balance: Val(Nz(dsum("[balance]","Union001","[LdgAccNo]=" & [LdgAccNo]),0)). It continue to return #Error.   What could be my dumb moment?
    Wednesday, March 30, 2016 1:48 PM
  • Do the fields balance and LdgAccNo occur in the Union001 query?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 30, 2016 4:55 PM