# How to test if numeric value in a text field is less than one ?

### Question

• tblX has fldX set as a text type with two character limit

frmX has a text field ffldX that displays the value entered

a decimal entry like .9 or .3, etc, is not a valid entry

only valid data are whole numbers between 1 and 15

testing CInt(ffldX) won't work as CInt(.9) returns a 1 as does .8, .7, etc

ditto for CLng(ffldX) it seems

So how can I check if ffldX contains a number begining with a "." ?

Thanks for any help.

Thursday, November 29, 2012 6:03 PM

• You will have to examine each character and test it is 0 to 9. If its max two characters, you could first test the length with Len() function then test each character with LEFT() and RIGHT() functions.

e.g.

If len(ffldX = 1) and Instr("0123456789",ffldx) <> 0 Then valid = true

if len(ffldx = 2) and Instr("0123456789",left(ffldx,1)) <> 0 and Instr("0123456789",right(ffldx,1)) <> 0 Then valid = true

Thursday, November 29, 2012 6:12 PM
• Instead of checking for invalid entries, check for valid entries.

I think I would use code in the text box's BeforeUpdate event procedure:

If Me.ffldX Like "##" Then
If Val(Me.ffldX) >= 1 And Val(Me.ffldX) <= 15 Then Exit Sub
ElseIf Me.ffldX Like "#" Then
If Val*Me.ffldX) >= 1 Then Exit Sub
Else
Beep
Me.ffldX.Undo
Cancel = True
End If

The equivalent expressions can be used in the field's or text box's Validation rule if you prefer.  (Actually, the table field's Validation rule is the safest way.)

(Like "##" And (Val(ffldX) >= 1 And Val(ffldX) <= 15) OR (Like "#" And  ffldX) <> 0)

Thursday, November 29, 2012 7:58 PM
• A better means of validation in a form would be to use the Control's KeyPress event procedure:

Select Case KeyAscii
Case 8               'backspace, accept
Case 48 To 57    ' number, accept
Case Else          ' reject
KeyAscii = 0
End Select

This would prevent all bar numeric characters and a backspace being entered.  Coupled with a ValidationRule of:

Val([ffldx])>=1 And Val([ffldx])<=15 And Left([ffldx],1)<>"0"

this should cover all bases.

Ken Sheridan, Stafford, England

Friday, November 30, 2012 11:47 AM

### All replies

• You will have to examine each character and test it is 0 to 9. If its max two characters, you could first test the length with Len() function then test each character with LEFT() and RIGHT() functions.

e.g.

If len(ffldX = 1) and Instr("0123456789",ffldx) <> 0 Then valid = true

if len(ffldx = 2) and Instr("0123456789",left(ffldx,1)) <> 0 and Instr("0123456789",right(ffldx,1)) <> 0 Then valid = true

Thursday, November 29, 2012 6:12 PM
• So how can I check if ffldX contains a number begining with a "." ?

Val([ffldX]) < 1

You can set the ValidationRule property of the field to:

Val([ffldx])  >=1 And Val([ffldx]) <=15

or if Nulls are allowed:

Is Null Or (Val([ffldx])>=1 And Val([ffldx])<=15)

Ken Sheridan, Stafford, England

Thursday, November 29, 2012 6:34 PM
• Instead of checking for invalid entries, check for valid entries.

I think I would use code in the text box's BeforeUpdate event procedure:

If Me.ffldX Like "##" Then
If Val(Me.ffldX) >= 1 And Val(Me.ffldX) <= 15 Then Exit Sub
ElseIf Me.ffldX Like "#" Then
If Val*Me.ffldX) >= 1 Then Exit Sub
Else
Beep
Me.ffldX.Undo
Cancel = True
End If

The equivalent expressions can be used in the field's or text box's Validation rule if you prefer.  (Actually, the table field's Validation rule is the safest way.)

(Like "##" And (Val(ffldX) >= 1 And Val(ffldX) <= 15) OR (Like "#" And  ffldX) <> 0)

Thursday, November 29, 2012 7:58 PM
• tblX has fldX set as a text type with two character limit

So how can I check if ffldX contains a number begining with a "." ?

(Another angle for you to consider.)

Don't forget that FieldX is a Text Field and there for you can check whether the first character is the period character in the Control_BeforeUpdate Event.  Something like:

```If Left(Me.YourControl, 1) = "." Then
' It is a period.  Do whatever '
Else
' It is not a period, Do something else '
End If```

Van Dinh

• Edited by Friday, November 30, 2012 2:08 AM
Friday, November 30, 2012 2:03 AM
• I think it is necessary to use Ken Sheridan's response as -2 (minus two) is less than 1 (one) but checking for decimal will not suffice.
Friday, November 30, 2012 2:12 AM
• A better means of validation in a form would be to use the Control's KeyPress event procedure:

Select Case KeyAscii
Case 8               'backspace, accept
Case 48 To 57    ' number, accept
Case Else          ' reject
KeyAscii = 0
End Select

This would prevent all bar numeric characters and a backspace being entered.  Coupled with a ValidationRule of:

Val([ffldx])>=1 And Val([ffldx])<=15 And Left([ffldx],1)<>"0"

this should cover all bases.

Ken Sheridan, Stafford, England

Friday, November 30, 2012 11:47 AM
• I often use the On Key Press event of a text box to control what keystrokes are allowed.

Invalid keystrokes are simply ignored.

Easier to use a function in a standard module, but here's how it would look for a single text box:

'==========================

Private Sub Area_KeyPress(KeyAscii As Integer)
On Error Resume Next

If KeyAscii= 39 Then 'single quote
KeyAscii = 0
Exit Sub
End If

If InStr("0123456789", Chr(KeyAscii)) > 0 Then
Exit Sub

Else

Select Case KeyAscii
Case vbKeyBack, vbKeyTab, vbKeyReturn
Exit Sub
Case vbKeyLeft, vbKeyRight
Exit Sub
Case Else
KeyAscii= 0

End Select

End If

End Sub

'==========================

Friday, November 30, 2012 1:39 PM
• only valid data are whole numbers between 1 and 15

Hi Mel_3,

You can test in the BeforeUpdate event for:

If ( IsNumeric(ffldX) ) then
If ( CInt(ffldX) - ffldX <> 0 ) Or ( ffldX < 1 ) Or ( ffldX > 15 ) Then Cancel = True
EndIf

Imb.

Friday, November 30, 2012 2:30 PM
• Hi Mel_3,

Welcome to the MSDN forum.

How is it going with the problem?

Please feel free to let us know if you need nay help.

Have a nice day.

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Friday, December 07, 2012 8:14 AM
• Hi Mel_3,

I temporarily marked the reply as answer and you can unmark it if it provides no help.

Please feel free to let us know if you need any help.

Thanks for your understanding and have a nice day.

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Monday, December 10, 2012 5:25 AM