Link to home
Create AccountLog in
Avatar of pdvsa
pdvsaFlag for United States of America

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Like this:

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

Open in new window


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
Avatar of 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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Avatar of pdvsa

ASKER

It was #1.  Thanks!  works perfectly.  
Avatar of pdvsa

ASKER

Dale, makes total sense.  I will change it.