iif with NZ and Sum in Query Builder (1 Viewer)

[YAM]

Hi everyone,

I am trying to do some calculated expression in my query with multiple combination of (iif,Sum,NZ)

Balance: Sum(IIf([GroupName]="Asset",[SumOfCurDebit]-[SumOfCurCredit],IIf([GroupName]="Liability",[SumOfCurCredit]-[SumOfCurDebit],IIf([GroupName]="Equity",[SumOfCurCredit]-[SumOfCurDebit],IIf([GroupName]="Income",[SumOfCurCredit]-[SumOfCurDebit],IIf([GroupName]="Expense",[SumOfCurDebit]-[SumOfCurCredit]))))))

In above code i am trying to subs tract SumOfCurDebit from SumofCurCredit based on iif condition of GroupName till here is ok with above.

What i want is if SumofCurCredit is Null and SumofCurDebit have value then it should show the value of SumofCurDebit only then and vise versa for SumofCurCredit with above code.

I am facing little issues with handling the null or black field value in Expression builder of my query, dont know where exactly i have to add the NZ function to calculate the null field value along with above code.

 

[Gasman]

Why not just have two columns? One is Credit-Debit, the other Debit-Credit and along with Groupname, choose which value to use?
You could even simplify that by just having the two options.

Regardless the NZ() should be around the amount fields, however the Sum() function ignores Nulls?

 

[arnelgp]

to make it simpler, create a UDF and call it within your query:

Public Function fncBalance(ByVal GroupName As String, dDebit As Variant, dCredit As Variant) As Double
    dDebit = Nz(dDebit, 0)
    dCredit = Nz(dCredit, 0)
    Select Case GroupName
        Case "Asset", "Expense"
            fncBalance = dDebit - dCredit
        Case "Liability", "Equity", "Income"
            fncBalance = dCredit - dDebit
    End Select
End Function

on your Query:

Balance : Sum(fncBalance([GroupName], [SumOfCurDebit], [SumOfCurCredit]))

 

[YAM]

Thanks Arnelgp

 

[arnelgp]

you're welcome