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
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.

Solutions 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"
``````

Michael
0
Commented:
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
``````
0
Solutions 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"
...
``````

Michael
0

Experts Exchange Solution brought to you by

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

Commented:
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
``````

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
Project financeAuthor Commented:
that worked.

mbizup:  I got an error.  Case is part.

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

thank you
0
Commented:
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
Project 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
Commented:
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
Project 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
Project financeAuthor Commented:
thank you fyed!
0
Commented:
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.