CODEWITHSUNDEEP
ms-access
Paul D
Paul D

Reputation: 23

using a SUM IF formula in Access

I am trying to sum "Order Qty" from a table in my Access database where "Material group name" equals "Generic Rx" through a Sum if formula

I am using:

Gen Ordered: SUM(IIF([Material group Name]=(Generic Rx).[Order Qty],0)

but I am getting an invalid syntax error at the Generic Rx expression

"Generic Rx" is value within the "Material group name" column

what is the best way to go about this? Thanks

Upvotes: 1

Views: 51977

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112762

Your question is unclear as you don't explain us what the different elements are. If we assume that Generic Rx is a query parameter and Order Qty is a table column then your expression must be

Gen Ordered:     Sum( IIF([Material group Name] = [Generic Rx], [Order Qty],    0) )
'                                                                  |            |
'Name of                  |<-------------- condition ------->|   true-part  false part
'result column
'                     IIF( <condition>, <result if true>, <result if false>)

Meaning: If Material group Name equals Generic Rx Then the result will be Order Qty else it will be 0.

Upvotes: 1

HansUp
HansUp

Reputation: 97131

SUM(IIF([Material group Name]=(Generic Rx).[Order Qty],0)
                              |           |              |
                              1           2              3
  1. Use quotes instead of parentheses to delimit literal text values.
  2. Use commas between IIf arguments.
  3. Every open parenthesis must be balanced by a close parenthesis.

This should be closer ...

SUM(IIF([Material group Name]="Generic Rx",[Order Qty],0))

Upvotes: 2

Related Questions