Solved DSum multiple fields with criteria (1 Viewer)

[E9-Tech]

I have the below expression in the Control Source in a form which is working for the one field to sum

=DSum("BasicPay","tblPayslip","[payslipDate]>=Forms!FrmPayslip![FYFrom] And [payslipDate]<= Forms!FrmPayslip!PayslipDate")

As well as the sum of BasicPay I need to add other fields within the same tblPayslip under the same criteria.
I need sum the BasicPay + PensionPay + OtherP1 + Other P2 from the same table.

Is this possible with DSum? if so what would be the code?

 

[Minty]

Have you tried

=DSum("BasicPay + PensionPay + OtherP1 + Other P2","tblPayslip","[payslipDate]>=Forms!FrmPayslip![FYFrom] And [payslipDate]<= Forms!FrmPayslip!PayslipDate")

I haven't but it would only take 2 minutes.
If you have any null values it probably won't work.

 

[Gasman]

Works for me?

? dsum("carbscalc + sugarcalc + fibrecalc","tbldaily","dailyID=12")
 2.25

 

[E9-Tech]

Thanks, I should have thought of pure maths rather than coding....

 

[Pat Hartman]

Be careful using plain ol' math. If a field contains null, the result of the calculation will be null. So, know your data. If you allow null values (I do depending on the data field), you must account for the possibility when doing math and always protect yourself by using Nz()

? dsum("Nz(carbscalc,9) + Nz(sugarcalc,0) + Nz(fibrecalc,0)","tbldaily","dailyID=12")

When you use only a single column in a function Access handles the null for you.

? dsum("carbscalc","dailyID=12")

When you code math expressions in VBA, same problem. Use Nz()

 

[Gasman]

Be careful using plain ol' math. If a field contains null, the result of the calculation will be null. So, know your data. If you allow null values (I do depending on the data field), you must account for the possibility when doing math and always protect yourself by using Nz()

? dsum("Nz(carbscalc,9) + Nz(sugarcalc,0) + Nz(fibrecalc,0)","tbldaily","dailyID=12")

When you use only a single column in a function Access handles the null for you.

? dsum("carbscalc","dailyID=12")

When you code math expressions in VBA, same problem. Use Nz()

Thank you Pat.
That was just my example on a table, where I do the not recommended, and store calcs.