• Status: Solved
• Priority: Medium
• Security: Public
• Views: 356

# 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
0
pdvsa
• 5
• 3
• 2
• +1
1 Solution

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 5
• 3
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.