# 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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### jackd

##### Well-known Member
What is/are your table designs?

#### xenou

##### MrExcel MVP
This is the kind of thing you would want to put into a lookup table and then ... well ... look up ( select [crew] from [mylooktable] where [DayOfWeek] = 'Mon' and Shift = 2; )

#### Micron

##### Well-known Member
Or DLookup, or....
I can't see where it was said where this is wanted. Query? Code? Certainly not a table field.
The table and a select query or lookup might be the best idea, especially if it's to be done in code. However, in a query you'd end up with a subquery, no? And you shouldn't use DLookup in a query, really. You could also call a function from a query, or even use something like:

VBA Code:
``````Sub TestMultiCase(strDay As String, strShift As String)
Dim strValues As String
Dim aryValues

aryValues = Array(strDay, strShift)
strValues = Join(aryValues)
Select Case True
Case strValues = "Mon 2"
MsgBox "Monday shift 2, A-crew"
Case strValues = "Tues 2"
MsgBox "Tuesday shift2, A crew"
...and so on
End Select
End Sub``````
You'd call that by calling as testmulticase "Mon","2" I couldn't make sense of what the values are from that block of text in post 1 so I made up my own.

That is my first ever crack at passing an array to a Select Case block. Didn't know it could be done.
Was also thinking of Switch or Choose functions, but then again, they don't work everywhere and I don't even know where this is supposed to be done.

EDIT - Forgot to mention that I think the numbers have to be passed as text, or at least converted to text in the sub. Plus, this has to be a function in order to call it from a query if that's what is needed.

#### mlfuson

##### New Member

Sorry for the confusion, i was wanting to create a calculated field(crew) in a query. I get the Date and the Shift from a table. I've created a calculated field for Day of Week. I want to calculate the Crew for each line. The only one that's working is Mon shift 2 = A-Crew using:
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")))))))

#### xenou

##### MrExcel MVP
Have you considered using a lookup table? Creating a long-winded formula such as the one above is bad for all kinds of reasons.

#### mlfuson

##### New Member

Have you considered using a lookup table? Creating a long-winded formula such as the one above is bad for all kinds of reasons.
Actually, I'm fairly new to Access and didn't know you could have a lookup. I guess that's something i should learn about and consider

#### jackd

##### Well-known Member
Does the crew assignment ever vary? You have 2 shifts per Day, 7 days a week and 4 crews. What changes week to week etc?
Could you have occasions where there are 3 shifts?

#### Micron

##### Well-known Member
i was wanting to create a calculated field(crew) in a query
A table could work as I mentioned, but we haven't seen any data so I'd say it's just a good guess. Perhaps your other tables would not be relatable to this lookup table. Anyway, a query can call a function for each row as I mentioned. It's something to keep in mind if the table idea doesn't pan out.

#### mlfuson

##### New Member
Does the crew assignment ever vary? You have 2 shifts per Day, 7 days a week and 4 crews. What changes week to week etc?
Could you have occasions where there are 3 shifts?
Hello Jackd. We only run 2 shifts a day, or as on alternative work schedule we call it Crews. The database only shows shifts 2 and 3. Shift 2 being Dayshift(a-crew) and Shift 3(b-crew). On Fri, Sat, Sun & Mon, we have a C-crew. So I have to take what day it is and what shift the database says and decide which "Crew" it was run on. Sat and Sun usually runs just 1 shift. The other half of the day is for maintenance time, unless the company calls for a manditory makeup day, which is the SS-crew). So what i want is a calculated field in a query, to automatically show the Crew name.

Replies
3
Views
186
Replies
9
Views
132
Replies
1
Views
292
Replies
1
Views
136
Replies
5
Views
134

1,130,128
Messages
5,640,282
Members
417,133
Latest member
caaronh85

### 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?

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