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

 

[jackd]

What is/are your table designs?

 

[xenou]

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]

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:

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]

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]

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]

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]

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]

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]

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.