If then statement help

Experts,

How would I code the below?  I know there is some fancy way to do it without making separate If statements for each condition.   I tried it but cant do it and spending too much time on it.  

            If Me.txtSPRating.Column(0) <= 10 Then
                Me.cboRiskLevel = "Low"
            If Me.txtSPRating.Column(0) > 10 And Me.txtSPRating.Column(0) <= 17 Then
                Me.cboRiskLevel = "Medium"
             If Me.txtSPRating.Column(0) > 17 And Me.txtSPRating.Column(0) <= 22 Then
                Me.cboRiskLevel = "High"
             If Me.txtSPRating.Column(0) > 22 And Me.txtSPRating.Column(0) <= 24 Then
                 Me.cboRiskLevel = "TBD"

thank you
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
Select statements will not work in this case so If..Else statements are the way to go

If Me.txtSPRating.Column(0) <= 10 Then
   Me.cboRiskLevel = "Low"
Else If Me.txtSPRating.Column(0) <= 17 Then
   Me.cboRiskLevel = "Medium"
Else If Me.txtSPRating.Column(0) <= 22 Then
   Me.cboRiskLevel = "High"
Else Me.txtSPRating.Column(0) <= 24 Then
   Me.cboRiskLevel = "TBD"

Open in new window


Michael
0
mbizupCommented:
Try this:

SELECT Case Me.txtSPRating.Column(0) 
            Case is <= 10
                Me.cboRiskLevel = "Low"
            Case  is <= 17 Then
                Me.cboRiskLevel = "Medium"
             Case is <= 22 Then
                Me.cboRiskLevel = "High"
             Case  is <= 24 Then
                 Me.cboRiskLevel = "TBD"
End select

Open in new window

0
Michael FowlerSolutions ConsultantCommented:
Sorry, Select..Case will work.

Select Case Me.txtSPRating.Column(0)
   Case 0 To 10
      Me.cboRiskLevel = "Low"
   Case 11 To 17
      Me.cboRiskLevel = "Medium"
...

Open in new window


Michael
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
In my code above, leave off the Thens... copy/paste issue from your original post.  It should be:

SELECT Case Me.txtSPRating.Column(0) 
            Case is <= 10
                Me.cboRiskLevel = "Low"
            Case  is <= 17 
                Me.cboRiskLevel = "Medium"
             Case is <= 22 
                Me.cboRiskLevel = "High"
             Case  is <= 24 
                 Me.cboRiskLevel = "TBD"
End select

Open in new window


With a select case statement like this, you actually don't need the ranges, as long as the conditions are in sequential order like this.  The select case execution will stop at the first condition met, and set the combo value at that point.
0
pdvsaProject financeAuthor Commented:
that worked.  

mbizup:  I got an error.  Case is part.  

thank you
0
pdvsaProject financeAuthor Commented:
mbizup:  i think you now see where the error was.  "then"

thank you
0
Dale FyeCommented:
I think you are looking for the Switch function:

If the Bound Column in your combo box (txtSPRating) = 1 then you don't need the Column(0) reference

m.cboRiskLevel = Switch(Me.txtSPRating & "" = "", NULL,
                                              Me.txtSPRating <= 10, "Low",
                                              Me.txtSPRating <= 17, "Medium",
                                              Me.txtSPRating <= 22, "High",
                                              Me.txtSPRating <= 24, "TBD",
                                              True, NULL)

Note that I added a condition at the beginning to test for a blank value in the combo box (nothing selected) and added another condition at the end to return a NULL if none of the previous conditions is met.
0
pdvsaProject financeAuthor Commented:
Hi fyed, thanks for that even when question is close out.  

I assume that goes in the afterupdate...I pasted it but it gives an error right after the NULL,

ok talk to you later.
0
Dale FyeCommented:
Yes, it should go in the AfterUpdate of the txtSPRating combo box.  Sorry, if you are going to span multiple rows like I did in the example above, you should use the continuation character ( _ ) at the end of each row, but you could just put it all in one line of code.

m.cboRiskLevel = Switch(Me.txtSPRating & "" = "", NULL, _
                                              Me.txtSPRating <= 10, "Low", _
                                              Me.txtSPRating <= 17, "Medium", _
                                              Me.txtSPRating <= 22, "High", _
                                              Me.txtSPRating <= 24, "TBD", _
                                              True, NULL)

The Switch function will evaluate a series of expressions and return the value associated with each expression.
0
pdvsaProject financeAuthor Commented:
ahh...I thought it was somethign like that but thought there needed to be an & in there too and not just the _

it works.  I like the Null part.  The case does not have this.  I tested.
0
pdvsaProject financeAuthor Commented:
thank you fyed!
0
Dale FyeCommented:
You could simply add the NULL part to the Case statement.  The advantage of the SWITCH function is that it can be used in a query, although if I need to use the SWITCH function, I generally prefer to create my own function.  This way, I can document it and describe why I selected the specific values that are used in each of the expressions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.