Multiple If then Statements

[boldcode]

Hello,

I am trying to fix the following If then statements code behind a command button. On a form I have a text box and a command button next to each other. In the text box users are only allowed to enter numbers. Once they entered a number they then click on the command button and it executes some code. What I want it do before it executes the main code is via the following if statements is to check that a user did not enter text first of all, then check that the number entered is between 1 and 99 because if it is not then it should exit the sub. If the data entered is not text and the number is between 1 and 99 then go ahead and execute the code.


txtTaxRate is an unbound text box
Go is a boolean variable (boolGo) that executes some code if true

If Not IsNumeric(txtTaxRate) then
MsgBox ("Please enter a number and not text.")
Exit Sub
ElseIf
If (Val(txtTaxRate) >= 1 and Val(txtTaxRate) <= 99) Then
    boolGo = True
ElseIf
If (Val(txtTaxRate) < 1 or Val(txtTaxRate) > 99) Then
boolGo = False
MsgBox ("Please enter a Tax Rate between 1 and 99")
Exit Sub
End If

boolGo = True

Thanks,

BC

 

[Norie]

Perhaps.

If Not IsNumeric(txtTaxRate) then
    MsgBox ("Please enter a number and not text.")
    Exit Sub
End If 

If (Val(txtTaxRate) >= 1 And Val(txtTaxRate) <= 99) Then
    boolGo = True
Else

    boolGo = False
    MsgBox ("Please enter a Tax Rate between 1 and 99")
    Exit Sub
End If

 

[boldcode]

That didn't work, thank you for your help.

 

[james_lankford]

That didn't work, thank you for your help.

what do you mean, it didn't work ?

how did it fail ?
what part of the logic did not work ?
did it catch that they entered text instead of a number or did it fail to catch that ?
did it catch that they entered a number < 0 and > 99 or did it fail to catch that ?

did the code itself fail to run or was there a runtime error ?

what if you put a breakpoint in there and step through the code ?

 

[boldcode]

I figured it out.

Here is the code below:

If txtTaxRate.Value < 1 Or txtTaxRate.Value > 99 Or IsNull (Me.txtFlatRate.Value) Then
MsgBox ("Enter tax rate between 1 and 99.")
boolGo = False
Else
boolGo = True
End If

 

[Norie]

Why don't you set up a validation rule on the field?

 

[boldcode]

I set that up on the text box and not the command button.

 

[Micron]

Val is pretty much useless here since the code exits if the textbox contains any non-numeric data. Aside from that, "didn't work" probably means "I got a 0 for a resut" (zero).
That's because if you don't pass a string to Val, you get a zero, and surrounding the textbox entry with "" won't help either. Certainly the addition of the property .value is not the reason for the success since the default property of a textbox is its value and does not have to be specifically referenced. The solution arrived at will allow spaces before the number. Try " 50" (without the quotes) and see. IsNumeric is the best choice IMHO. I think I would do it like this:

boolGo = False
If Not IsNumeric(txtTaxRate) then
    MsgBox ("Please enter a number and not text.")
    Exit Sub
End If 

If txtTaxRate >= 1 And txtTaxRate <= 99 Then boolGo = True

The benefit of setting a Boolean value at the start is twofold. 1) unexpected termination leaves the value in what you think should be the default state (in this case, False). 2) It doesn't take an If Else block to set it to the desired value if code passes a test.