Logic question in a WHERE clause using IIF?

• DaveBriCam

  SSCarpal Tunnel

  Points: 4315

  More actions

  August 21, 2026 at 8:10 pm

  #4945168

  Thanks in advance,

  I'm trying to avoid using IIF for performance reasons, but the code has me stumped.

  I'm trying to add a filter in my WHERE clause that basically says "omit records if [BillingReason] in (1, 2, 3) AND ([NewInsID] IS NOT NULL AND [OldInsID] IS NOT NULL)". For example:

  [BillingReason] = 1, [NewInsID] = Null, [OldInsID] =Null -- Omit this record

  [BillingReason] = 4, [NewInsID] = Null, [OldInsID] =Null -- Include this record

  [BillingReason] = 1, [NewInsID] = 123456, [OldInsID] = 456789 -- Include this record

  [BillingReason] = 2, [NewInsID] = 123456, [OldInsID] = 456789 -- Include this record

  [BillingReason] = 3, [NewInsID] = Null, [OldInsID] = Null -- Omit this record