asked on

Summing on a form

Experts,

How can I modify this in a textbox on a form:
=Sum([tblPayments.Amount])

To only sum if NotPaidYN = False
In other words, I only need to sum the tblPayments.amount if NotPaidYN = False

thank you

Gustav Brock

Like this:

=Sum(IIf(Not [NotPaidYN],[tblPayments.Amount],0))

Open in new window

Ryan Chong

I believe what Gustav mentioned should resolved your issue?

Just in case if it doesn't and you mean to "sum all relevant amount values if its correspondence's field: NotPaidYN = False", then use DSUM function instead.

DSum ( expr , domain [, criteria] )

such as:

=DSum("Amount", "tblPayments", "NotPaidYN = False")

Open in new window

more details

DSum Function
https://support.microsoft.com/en-us/office/dsum-function-08f8450e-3bf6-45e2-936f-386056e61a32?ui=en-us&rs=en-us&ad=us

pdvsa

ASKER

Ryan,... understood. Thanks for the tip.

Gustav: do I need an extra parenthesis or something else? I am getting wrong number of arguments and I have tried many combinations.

Thank you
Typing from phone.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Personally, I avoid negatives in field and variable names, it just makes them harder to deal with and understand in your code.
Instead of [NotPaidYN] I would have used [PaidYN]

Dale

pdvsa

ASKER

It was #1. Thanks! works perfectly.

pdvsa

ASKER

Dale, makes total sense. I will change it.