CODEWITHSUNDEEP
ms-access
mp11477
mp11477

Reputation: 13

Adding multiple number fields together based on yes/no answers in Access

It's been years since I've worked with Access/VBA so most of what I learned has been forgotten. That said, I'm trying to create a table with multiple numeric fields that I would like to add together on a form based on whether or not someone selected yes/no.

I have the following type of setup

Field_A   (Number)
Field_A_Check   (yes/no)
Field_B   (Number)
Field_B_Check  (yes/no)
Field_C   (Number)
Field_C_Check   (yes/no)

If someone clicks yes next to Field_A and Field_C, I want to calculate the total in Field_A and Field_C together and capture that total on a form text box. If someone else clicks yes next to Field_B only, I want to calculate just that number for that field only.

I've done a nested iif statement on the form, but it became so convoluted, I'd get lost. It also doesn't seem like the best way to tackle this either. Any additional help would be appreciated.

Thank you.

Upvotes: 1

Views: 55

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

Since true & false boolean field values are represented by the integers -1 & 0 respectively, you can use the following single expression to calculate the total:

=-Field_A*Field_A_Check-Field_B*Field_B_Check-Field_C*Field_C_Check

Here, if any of the _Check fields are unchecked (false), the field value will be zero thus omitting the corresponding value from the sum.

Upvotes: 0

Related Questions