I am having difficulty calculating the sum of a number field for only those data records that match a specific criterion.

In the SQL Database I am working with two fields:

InspectionDefects.DispCode and InspectionDefects.NumberDefectiveParts.

What I am trying to do is calculate the sum of the NumberDefectiveParts field for only the records where the DispCode = 'RTS'.

I have tried the IIF statement of:

iif({InspectionDefects.DispCode}= 'RTS',Sum ({InspectionDefects.NumberDefectiveParts}, {Inspection.InspectionDate}, "monthly"),0)

This doesn’t work for me.  If there are any records where the DispCode <> ‘RTS’ then the Formula Field Value always returns 0.

Example:  If I have 5 records with the following values (DispCode), (NumberDefectiveParts).

RTS, 3

ACC, 3

RTS, 1

RTS, 5

ACC, 2

The value of the formula fields should be nine (9) the sum of the NumberDefectiveParts where DispCode = ‘RTS’ or 3+1+5 = 9.

Any help would be most appreciated.