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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,318
Office Version
  1. 365
What is/are your table designs?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,830
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
4
Office Version
  1. 2016

ADVERTISEMENT

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")))))))
Annotation 2020-12-03 083047.png
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,830
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
4
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
Oct 19, 2006
Messages
1,318
Office Version
  1. 365
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
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
4
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,837
Messages
5,638,639
Members
417,039
Latest member
Sworks

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