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.

Answer 1

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

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

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

Answer 8

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.

Answer 9

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.

Answer 10

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.