Iif, If Then Else, and Select Case in MS Access

i'm your host sean mckenzie thank you for joining me once again on my channel on data analytics and data engineering this week we're going to ask the question what if we're going to cover a discussion about if then else statements and select case statements and we're also going to talk about a nice little uh function called if that is very handy and useful for both expressions and vba coding so without further ado let's get to our if statements okay so we're going to get started here and i'm using the same file that you've seen me use in the past few microsoft access videos and we've got a visit table here that has some data in it that we used for another example and we'll use it for today and our first example will be all about whether somebody's angry or not as you can see each person has a different mood at a different time in that table so what we're going to do is we'll create a module for our first example and we're going to create a function that has an if if-then else statement in it and that's going to help us to determine whether a person is angry in each row and so i'll resize this here and so what we'll do is we're going to start off by we'll we'll create a function so we'll we'll say function is angry and then for that function we're going to pass in whatever mood they're feeling at at this moment and so we'll i'll call it var mood because it's a variant just in case there's a null in there that'll take care of it as well and uh so we'll comment it and say you know says whether he's angry or not and then we'll use uh if um var mood equals uh angry then i will say is angry uh we'll will return like a sentence i guess saying is angry equals uh he's angry and and and then i can't type today and then i will say else is angry equals uh he's not angry just as an example um and that's a very simple way of using if then else and we'll end it off with an end if and as you can see that's our little function there we've got our if then and then else if it's not the first case and then we have to put our end if on the end of it it's a very handy way of doing it and we'll save our our module and then i'll minimize that and we'll go ahead and make a query on our visit table so we'll create a query design you can just close that little table that little pop up there and just drag your table on and uh we'll select i guess um i guess we'll just select everything and uh and then at the end of our last or at our last column what we'll do is we'll we'll say the field called angry is equal to or is is angry and then we'll put in our mood field so you got to use your square brackets for that and then we'll save save that one as uh i guess we'll call it uh um angry query something like that and we'll after we've saved that one we can go ahead and we'll double click to to open it i guess we'll close this out here we could just do it straight from there but we'll close the design and open the query just to demonstrate how that's done and now you can see we've got a last column on here that checks whether the mood is angry or not says angry and you can see we've got one that's that's angry but everybody else is not angry and that's probably a good thing so what we'll do is we'll close that and we'll save the layout and then and then we can go on to our next our next example so in this example we'll open our design view and we're going to use we're going to do the same thing that we did using a procedure or a function in vba and instead we're going to use what's called an expression and we're going to we're going to type directly in here and we're going to use in our expression we're going to use if which is iif and we'll say if the field mood in square brackets is angry then we will say he's angry uh then i can't type here we'll say he's angry and then use a comma to say for the alternate outcome which is not angry and so we'll say he's not angry so this is a handy way of doing a very simple if then statement where it's just values that are being compared and and simple outcomes a single simple outcome you can do it this way using iif and so that's in an expression we'll double click to open that and you can see we get the same outcome using iif as we did in writing a procedure however as you use these more and more uh you'll discover the difference between the two iaf as we see here is for very simple cases where it's a very simple comparison and then a simple outcome and the function allows you to do a lot more than the iif statement so you could go ahead and do a whole bunch of actions on each case where it says angry you could update some records or you could you know alert some users or you could do all kinds of things and then you could do different actions if the person was not angry and you could have all kinds of things that happen when in each case and so that's why the function is a lot more versatile than using the iif statement so in our next example what we're going to do is we're going to use the select case statement which allows you to have a lot more flexibility and outcomes than using just if then else and so if you have a situation where there's many things to choose from and there's many outcomes for each you know for each case then then you can use select case and so in this case we could say if somebody's uh good if they're feeling positive then that's we'll put them in if they're good then in the positive category if they're nervous we'll put them in the middle category and if they're angry we'll say that they're in a sort of uh negative um category and so we'll put depressed in the negative category and some other ones and so we'll go back to our module that we created and we'll create another function we'll call it positive middle and negative it's a very long function name but we'll we'll call it positive middle negative and then uh what this function is going to do is it's going to allow us to have several inputs for each one and we'll pass in the the mood variant just in the same way we did for the is angry function and variant just means it could be anything including a null value so a string if we put the the argument as a string then then it might have a null in the database field and that would cause an error so what we'll do is we'll say we'll say select case var mood and then we'll say case angry l and then sad and i think we had something else on there depressed okay so we'll say angry sad and depressed um we'll say that that's a negative feeling i can't type here uh so in case angry sad and depressed and then we'll say our function positive middle negative is equal to uh negative and that'll pass back the the word negative if if the mood is one of those and then we'll say if their case uh nervous i think nervous is the only one in the middle category the other ones are all positive or negative um so yeah we'll just say uh case nervous we'll say case nervous uh and we'll say that is a middle so we'll say positive middle negative is equal to middle and that will allow for that case to be to be passed and then we'll say case uh good and happy need a case uh good and happy and we'll say that those are positive uh so we'll say positive middle negative is equal to uh positive and one thing you you want to do whenever you do a select case statement is you always want to add a case else just in case there's some other value and you want to handle something that isn't one of all the ones that you put above and so you always want to put a case else in there and we'll see other if that happens and then we can save that one just the same as we did the others and then from there we'll go back to our design view of our angry query and we'll just add in a new field and uh in this field what we'll do is we'll we'll call this one positive the positive field and and so we'll say positive and what we'll do is we'll add our positive middle negative function and then we'll say bracket um and we'll add our mood and square brackets that's the the value that's in the mood field and we'll put a closing bracket on there as well and that's going to allow us to to see what happens there and so we'll close and run that one and then what you can see is now we also have another field called positive and it evaluates based on our select case statement you know whether they're in a positive middle or negative kind of mood today and that's how you do if then else and select case in microsoft access i hope you enjoyed our discussion today about using if-then-else statements and select case and i if in different situations and if you like what you saw today please give it a thumbs up and subscribe to the channel if you haven't subscribed yet and when you subscribe make sure you click on the bell so that you'll be notified of any new content that i put up on the channel and if you have any questions or comments please go ahead and leave those in the comment section below i'll be happy to answer any questions that you might have have a safe day and i'll catch you next [Music] [Applause] time [Music] so [Music] you

Channel: Sean MacKenzie Data Engineering

Views: 5,393

Rating: 4.9230771 out of 5

Keywords: if then else statement, ms access, select case sql, ms access tutorial, ms access database, if then else in access query, if then else in access, select case in access vba, ms access if then else, vba if then else, vba select case example, vba iif, vba iif vs if, access iif function, access iif expression, iif in access query expression, sean mackenzie

Id: E5HbVmxHxSQ

Length: 13min 1sec (781 seconds)

Published: Fri Aug 14 2020