By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,457 Online
Bytes IT Community
+ Ask a Question
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

lee123
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
Share this Question
Share on Google+
11 Replies


missinglinq
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
  1. Private Sub ZipCode_AfterUpdate()
  2. Select Case ZipCode
  3.   Case 23139
  4.    Me.City = "Powhatan"
  5.   Case 23803 To 23805
  6.    Me.City = "Petersburg"
  7.   Case 23111, 23116
  8.    Me.City = "Mechanicsville"
  9.   Case 23220 To 23231, 23234
  10.    Me.City = "Richmond"
  11.   Case Else
  12.    Me.City = "Unknown"
  13. End Select
  14. End Sub
  15.  
"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

missinglinq
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
  1. Select Case PointAverage
  2. Case Is > 5.5
  3.   Me.Rating = "U"
  4. Case Is > 4.5
  5.    Me.Rating = "E"
  6. Case Is > 3.5
  7.    Me.Rating = "D"
  8. Case Is > 2.5
  9.    Me.Rating = "C"
  10. Case Is > 1.5
  11.    Me.Rating = "B"
  12. Case Is > 0
  13.    Me.Rating = "A"
  14. End Select
Linq ;0)>
Mar 13 '08 #3

lee123
100+
P: 556
Thanks missinglinq this will help me out alot in my programming.

lee123
Mar 13 '08 #4

missinglinq
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
  1. Private Sub ReportSelection_AfterUpdate()
  2.  
  3. Select Case ReportSelection
  4.  Case "New Personnel"
  5.    DoCmd.OpenReport "NewHires", acViewNormal
  6.  Case "Absentees"
  7.   DoCmd.OpenReport "MissedTime", acViewNormal
  8.  Case "Vacations"
  9.   DoCmd.OpenReport "RequestedTiemOff", acViewNormal
  10. End Select
  11.  
  12. End Sub
Linq ;0)>
Mar 13 '08 #5

lee123
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
  1. Private Sub Name_AfterUpdate()
  2.  
  3.     Select Case Name
  4.         Case "jerry"
  5.             LastName = "goods"
  6.         End Select
  7.  
  8. 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
  1. Private Sub Name_AfterUpdate()
  2.  
  3.     Select Case Name
  4.         Case "FF"
  5.             LastName = "goods"
  6.         End Select
  7.  
  8. 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

NeoPa
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
  1. Select Case True
  2. Case Var1 > 74, Var2 = "Success"
  3.   Call MsgBox("Success")
  4. Case Var1 > 49, Var2 = "Please Retry"
  5.   Call MsgBox("Please Retry")
  6. Case Else
  7.   Call MsgBox("Failure")
  8. End Select
Mar 14 '08 #7

NeoPa
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
  1. Private Sub Name_AfterUpdate()
  2.   Select Case UCase(Me.Name)
  3.   Case "JERRY"
  4.     LastName = "Goods"
  5.   End Select
  6. End Sub
Mar 14 '08 #8

lee123
100+
P: 556
hi neopa thanks i'll try it and thanks for the other example

lee123
Mar 14 '08 #9

missinglinq
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

lee123
100+
P: 556
thanks linq I'll try it.

lee123
Mar 14 '08 #11

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.