434,806 Members | 1,457 Online
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

# Understanding the Select Case Statement

 100+ P: 556 hi there i want to understand the "Select Case Statement" fully and the way i think i would is to use it in a real situation such as using a certain "City and Zipcodes" lets say a user enters in a certain City and have the zipcode pop in the textbox. can anyone help me with this understanding? lee123 Mar 13 '08 #1
11 Replies

 Expert 2.5K+ P: 3,532 That's a poor example, because a city can have many, many Zipcodes. A much better example would be just the opposite! The user enters a Zipcode and the correct city would populate the textbox. This is good because it allows the demonstration of a number of ways presenting the case. Expand|Select|Wrap|Line Numbers Private Sub ZipCode_AfterUpdate() Select Case ZipCode   Case 23139    Me.City = "Powhatan"   Case 23803 To 23805    Me.City = "Petersburg"   Case 23111, 23116    Me.City = "Mechanicsville"   Case 23220 To 23231, 23234    Me.City = "Richmond"   Case Else    Me.City = "Unknown" End Select End Sub   "Powhatan" shows a single value for the case expression. "Petersburg" shows the use with a range of values for the case expression. "Mechanicsville" shows the use when multiple, non-consecutive values are present. "Richmond" shows the use with a combination of consecutive and non-consecutive values. The final Case Else allows for the event that none of the Case expressions has been entered. Linq ;0)> Mar 13 '08 #2

 Expert 2.5K+ P: 3,532 Here's another little known way to use Select Case. In this case, an average is being derived at and Ratings assigned accordingly. If the PointAverage is above a certain value, the Rating is thus and so. If the PointAverage was, for the sake of this demo, 6.0, then the Case expression would satisfy every Case, because 6.0 is greater than 0, 1.5, 2.5, 3.5, 4.5 and 5.5! So which value is assigned to the control Rating? In the Select Case construct, if a value satisfies more than one Case, the first Case that is satisfied is the one that is executed! To take advantage of this, you construct your Select Case in descending order, i.e. with the highest value first. So in this case, Rating would be assigned "U." Likewise, with a PointAverage of 4.7, Rating would be assigned "E" because Case Is > 4.5 is the first Case that 4.7 satisfies. Expand|Select|Wrap|Line Numbers Select Case PointAverage Case Is > 5.5   Me.Rating = "U" Case Is > 4.5    Me.Rating = "E" Case Is > 3.5    Me.Rating = "D" Case Is > 2.5    Me.Rating = "C" Case Is > 1.5    Me.Rating = "B" Case Is > 0    Me.Rating = "A" End Select Linq ;0)> Mar 13 '08 #3

 100+ P: 556 Thanks missinglinq this will help me out alot in my programming. lee123 Mar 13 '08 #4

 Expert 2.5K+ P: 3,532 You can also use Select Case with string values and to do things other than assigning values to other controls. This example uses it to decide which reports to open: Expand|Select|Wrap|Line Numbers Private Sub ReportSelection_AfterUpdate()   Select Case ReportSelection  Case "New Personnel"    DoCmd.OpenReport "NewHires", acViewNormal  Case "Absentees"   DoCmd.OpenReport "MissedTime", acViewNormal  Case "Vacations"   DoCmd.OpenReport "RequestedTiemOff", acViewNormal End Select   End Sub Linq ;0)> Mar 13 '08 #5

 100+ P: 556 hey linq i tried a little experiment with the select case method and it didn't work. i put two textboxes (unbound) and named one name & the other one Lastname, but when i did the code. it didn't work i entered it like this: Expand|Select|Wrap|Line Numbers Private Sub Name_AfterUpdate()       Select Case Name         Case "jerry"             LastName = "goods"         End Select   End Sub maybe i did this wrong but inorder for me to get the full understanding i wanted to try it out. so i did and what do you know it didn't work. Then i did this: Expand|Select|Wrap|Line Numbers Private Sub Name_AfterUpdate()       Select Case Name         Case "FF"             LastName = "goods"         End Select   End Sub And this one worked. why is that. I also sub. the "FF" with a number (With No Quotes) and nothing. but the two letters worked. is this strange or did i miss something about this. lee123 Mar 14 '08 #6

 Expert Mod 15k+ P: 31,489 Firstly to respond to Linq's excellent and illustrative examples, but I will get back to your last question later Lee. I recently found another way of using the Select Case construct for when the checks were not all comparing a single variable or state. It involves doing things logically 'A... about Face' if I can use that term. Otherwise think 'Backwards'. Say you want to flag something to the operator if any of a number of conditions were true, then you could make something like : Expand|Select|Wrap|Line Numbers Select Case True Case Var1 > 74, Var2 = "Success"   Call MsgBox("Success") Case Var1 > 49, Var2 = "Please Retry"   Call MsgBox("Please Retry") Case Else   Call MsgBox("Failure") End Select Mar 14 '08 #7

 Expert Mod 15k+ P: 31,489 Your code looks fine in both cases Lee, although I would take a closer look at the indenting used either in my example or in Linq's. Using Select Case can be very powerful, but wrong indenting can make it very complicated to read correctly. We don't know how it failed or what data was entered (I'm thinking case particularly here) so I can be little further help at this time except to say that the code looked as if it should work. Look to the data entered. Alternatively, try : Expand|Select|Wrap|Line Numbers Private Sub Name_AfterUpdate()   Select Case UCase(Me.Name)   Case "JERRY"     LastName = "Goods"   End Select End Sub Mar 14 '08 #8

 100+ P: 556 hi neopa thanks i'll try it and thanks for the other example lee123 Mar 14 '08 #9

 Expert 2.5K+ P: 3,532 That's an interesting application of it, Ade! lee123, go into your code window behind your form and go to the very top of it. I think you'll either not see any Option Compare statement, or you'll see the statement Option Compare Binary. Absent an Option Compare statement, it defaults to Binary. This means that all comparison (like the Case statements) are case sensitive. Change this statement or if missing simply enter Option Compare Database. That should solve your problems. Linq ;0)> Mar 14 '08 #10

 100+ P: 556 thanks linq I'll try it. lee123 Mar 14 '08 #11

 Expert Mod 15k+ P: 31,489 I've split another (hijack) question off into its own thread. Interested parties will find it here (Find Using Wildcard). Mar 14 '08 #12