Q:

MS Access filtering

I'm trying to filter the following data in access using SQL view and design view. What I basically need is if company is company 2, then I only want to consider rows with ERP ref number "INV and EXP". For all other company's I would like to include everything.
Company Status  ERP Reference Number    Company Code    Supplier ID + Name
Company 1   Completed in Workflow   INV Finance 200350033 - ATRIUM STAFFING LLC
Company 1   Completed in Workflow   ss  Corporate   200350033 - ATRIUM STAFFING LLC
Company 1   Completed in Workflow   SS  Payroll 200350033 - ATRIUM STAFFING LLC
Company 1   Completed in Workflow   INV HR  200350033 - ATRIUM STAFFING LLC
Company 1   Completed in Workflow   SS  Finance 200350033 - ATRIUM STAFFING LLC
Company 2   Completed in Workflow   SS  Corporate   200350033 - ATRIUM STAFFING LLC
Company 2   Completed in Workflow   INV Payroll 200350033 - ATRIUM STAFFING LLC
Company 2   Completed in Workflow   ss  HR  200350033 - ATRIUM STAFFING LLC
Company 2   Rejected from Workflow  ss  Payroll 200350033 - ATRIUM STAFFING LLC
Company 3   Completed in Data Entry EXP HR  200152751 - KONE INC.
Company 3   Completed in Workflow   SS  HR  200364067 - ROTH BROS,INC
Company 3   Completed in Data Entry EXP HR  200152751 - KONE INC.
Company 3   Completed in Data Entry tsi Finance 200152751 - KONE INC.
Company 3   Completed in Data Entry TSI Corporate   200152751 - KONE INC.

Here is my code under SQL view:
IIf([DFM report].[Company]="Company 1", [ERP Reference Number] like "%INV%' AND [ERP Reference Number] like '%EXP%', " ")

A:

Use SWITCH to check different conditions in Access (it's kind of like the MS Access version of CASE).
SELECT *
FROM TABLE
WHERE (SWITCH(
    [DFM report].[Company]="Company 1" AND [ERP Reference Number] = "INV", 1,
    [DFM report].[Company]="Company 1" AND [ERP Reference Number] = "EXP", 1,
    [DFM report].[Company]<>"Company 1", 1, 
    TRUE, 0)) = 1

