none
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

Answers

  • 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 Van DinhMVP 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
    Moderator
  • 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
    Moderator