Using if and or statement in access

[mlfuson]

Good morning:
I'm using Access 2016 and I'm trying to calculate which shift (or crew in my case). We have an alternative work schedule as described below:
Monday shift 2=A-crew
shift 3=C-Crew
Tuesday shift 2= A-Crew
shift 3= B-Crew
Wednesday shift 2=A-Crew
shift 3=B-crew
Thursday shift 2=A-Crew
shift 3=B-Crew
Friday shift 2=C-Crew
shift 3=B-Crew
Saturday shift 2=C-Crew
shift 3=SS-Crew
Sunday shift 2=SS-Crew
shift 3=C-Crew

My data gives me the Day and Shift. How can I create a calculated field to show which Crew.
I've tried
CREW:
IIf(([Day of Week]="Mon" And [SHIFT]=2),"A-CREW","C-CREW" Or
IIf(([Day of Week]="Tue" And [SHIFT]=2),"A-CREW","B-CREW" Or
IIf(([Day of Week]="Wed" And [SHIFT]=2),"A-CREW","B-CREW" Or
IIf(([Day of Week]="Thu" And [SHIFT]=2),"A-CREW","B-CREW" Or
IIf(([Day of Week]="Fri" And [SHIFT]=2),"C-CREW","B-CREW" Or
IIf(([Day of Week]="Sat" And [SHIFT]=2),"C-CREW","SS-CREW" Or
IIf(([Day of Week]="Sun" And [SHIFT]=2),"SS-CREW","C-CREW")))))))

And


IIf([Day of Week]="Mon" AND [Shift]=2) THEN "A-crew"
ELSEIF ([Day of Week]="Mon" AND [Shift]=3) THEN "C-crew"
ELSEIF ([Day of Week]="Tue" AND [Shift]=2) THEN "A-crew"
ELSEIF ([Day of Week]="Tue" AND [Shift]=3) THEN "B-crew"
ELSEIF ([Day of Week]="Wed" AND [Shift]=2) THEN "A-crew"
ELSEIF ([Day of Week]="Wed" AND [Shift]=3) THEN "B-crew"
ELSEIF ([Day of Week]="Thu" AND [Shift]=2) THEN "A-crew"
ELSEIF ([Day of Week]="Thu" AND [Shift]=3) THEN "B-crew"
ELSEIF ([Day of Week]="Fri" AND [Shift]=2) THEN "C-crew"
ELSEIF ([Day of Week]="Fri" AND [Shift]=3) THEN "B-crew"
ELSEIF ([Day of Week]="Sat" AND [Shift]=2) THEN "C-crew"
ELSEIF ([Day of Week]="Sat" AND [Shift]=3) THEN "SS-crew"
ELSEIF ([Day of Week]="Sun" AND [Shift]=2) THEN "SS-crew"
ELSEIF ([Day of Week]="Sun" AND [Shift]=3) THEN "C-crew"
END

Neither seem to work. Help

 

[xenou]

Well I have to apologize I can only really test TSQL at work:

Here's an example of creating a lookup table:

drop table if exists #CrewLookup
create table #CrewLookup (
    WeekDayName varchar(255),
    ShiftNum int,
    Crew varchar(255)
)
insert into #CrewLookup (WeekDayName, ShiftNum, Crew)
    values
     ('Sun','1','')
    ,('Sun','2','SS')
    ,('Sun','3','C')
    ,('Mon','1','')
    ,('Mon','2','A')
    ,('Mon','3','C')
    ,('Tue','1','')
    ,('Tue','2','A')
    ,('Tue','3','B')
    ,('Wed','1','')
    ,('Wed','2','A')
    ,('Wed','3','B')
    ,('Thu','1','')
    ,('Thu','2','A')
    ,('Thu','3','B')
    ,('Fri','1','')
    ,('Fri','2','C')
    ,('Fri','3','B')
    ,('Sat','1','')
    ,('Sat','2','C')
    ,('Sat','3','SS')

Here's some test data:

drop table if exists #Test
create table #Test (
    ShiftDate date,
    ShiftNum int
)
insert into #Test values
    ('2020-12-03', 2),
    ('2020-12-03', 3),
    ('2020-12-04', 2),
    ('2020-12-04', 3),
    ('2020-12-05', 2),
    ('2020-12-05', 3),
    ('2020-12-06', 2),
    ('2020-12-06', 3),
    ('2020-12-07', 2),
    ('2020-12-07', 3),
    ('2020-12-08', 2),
    ('2020-12-08', 3),
    ('2020-12-09', 2),
    ('2020-12-09', 3)

And here's the all the goodness of lookup tables to get your result:

select
    test.ShiftDate,
    test.ShiftNum,
    crew.Crew
from
    #Test test
    inner join #CrewLookup crew
    on substring(datename(weekday, test.ShiftDate), 1, 3) = crew.WeekDayName
    and test.ShiftNum = crew.ShiftNum

MSAccess won't have the datename() function so that will need adaptation. Also you can't use my test tables as written. But you can see what's happening I think.

MSAccess will give you the additional possibility of using the DLookup() function, so something like:
select ShiftDate, ShiftNum, DLookup(...) as Crew from MyTable;