# Using if and or statement in access

#### mlfuson

##### New Member
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

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

Here's an example of creating a lookup table:
SQL:
``````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:
SQL:
``````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:
SQL:
``````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;

Last edited:

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Replies
3
Views
179
Replies
9
Views
120
Replies
1
Views
283
Replies
1
Views
80
Replies
5
Views
126

1,127,445
Messages
5,624,814
Members
416,056
Latest member
VARSHA V VASWANI

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back