Using if and or statement in access

mlfuson

New Member
Joined
Dec 2, 2020
Messages
4
Office Version
  1. 2016
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
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top