From the course: Access: VBA

Select actions using a Case statement

From the course: Access: VBA

Start my 1-month free trial

Select actions using a Case statement

“

- [Instructor] The if then code construct let's you determine which code segments to run based on rules you define. But, I find it's syntax to be a bit convoluted. If you need to run a brief segment, such as, assigning a value to a variable based on another variable's value, you can use a CASE statement. A CASE statement is more compact than the if then construction, and I usually find that I'm less likely to make mistakes when I use CASE when I have to use more than three tests. So up to three, if, then, else and elseif works well, otherwise I go with CASE. In this movie I will provide an example of when I might use a CASE statement. I have opened the project database and I'll press Alt-F11 to move to the Visual Basic Editor. Now from here I can insert a code module, by going to the Insert menu and clicking Module. And I'll make the module window a little bit bigger in case I need the space. And I can define my subroutine. I'll call it Sub CalcComm, and then an open and closed parenthesis because I won't be passing any arguments. Then Enter a bunch of times. Now I can declare my variables. So I'll do Dim curTotal, as Currency then a comma, and curComm as Currency. So I have the order total and the commission that I'll calculate in a moment. I'll get the amount of the sale, that is the total through an input box. So I'll say, curTotal equals InputBox, left parenthesis then I'll put the prompt in double quotes 'cause it's a string, Enter the sale amount, close quotes, close the parenthesis. Everything looks good and now I can create my CASE statement. To start a CASE statement you use the Select Case construction and then you need to type the name of the variable and that's what we just got through the InputBox, curTotal. Then Enter and we will create a series of Case Is statements. So the first case will be fore a value of greater than or equal to 10,000. So it'll be Case Is, greater than or equal to 10,000 and note that you don't put the name of the variable here, you have already declared that on the first line of the Select Case construction. Now Enter and I will add a couple spaces, curComm equals curTotal, times zero point zero eight. So what we're doing here is if the value is greater than or equal to 10,000 then the commission will be the total multiplied by 8% or zero point zero eight. Now we'll do the same thing for three more cases. Backspace, Case Is greater than or equal to 1,000 and for that the commission will be 6%, so equals curTotal times zero point zero six, Enter. Case Is greater than or equal to 500, and for that the commission will be 5%. So curTotal times zero point zero five. Then we can wrap up our last Case as a catchall and that would be Case Else and if you're familiar with if then statements you see a lot of similarities. And that will be a commission of 5%, so curComm equals curTotal, times zero point zero four. Now we can end the Select Case construction with the keywords End Select. Enter. And, then a message box displaying the commission. So that would be MsgBox, left parenthesis quote The Commission is and then a dollar sign, double quote. Then an ampersand so that I can concatenate the value of the commission. curComm and right parenthesis and a down arrow. So let's review, we have an InputBox asking for a sale amount and then several different cases that look for values greater than or equal to 10,000, 1,000, 500 and then a Case Else that will handle everything else. It's very important that you put your most restrictive conditions first in your Select Case statement. That's why I have the highest value, in this case for greater than or equal to a 10,000 at the top, then 1,000, 500 and so on. If I were to reverse the order, of the first three statements, so Cases greater than or equal to 500 then any sale of greater than or equal to 500 would get a 5% commission, even if they might have qualified for the other as six or 8%. So again, always put your most restrictive condition at first because Select Case will stop as soon as it finds a condition that is met. Okay, with all that in place let's press F5 and run the code. Enter the sale amount, I'll put in 10,000. And hopefully we'll get $800. We do, click OK. And then I'll press F5 again, and 1,000. Okay, $60, 6%. Then for 500, so F5 again, 500. 25 bucks and then we'll do one for 100. So that should be $4. F5. 100, OK. And we get $4. Select Case statements let you evaluate a variable and take action based on that value. The easy to understand structure of the CASE statement let's you and your colleagues maintain your code effectively long after you create it.

Contents