Solved Entry restriction to numbers and single sign

[freuzo]

Hello everyone,
I have a text box where I would like to restrict the entry to numbers AND to the sign "-" (minus). The options I can think of are:
- either we type a character other than these, it receives an alert,
- or if we have typed the numbers AND another sign than the "-", in the background the typed sign is replaced by "-"

Thank you in advance for your suggestions.

 

[theDBguy]

Have you tried using an Input Mask?

 

[plog]

If the underlying field is a numeric type, Access will handle this for you by displaying a prompt that the input data isn't valid for the input.

 

[missinglinq]

The problem with letting the Access Gnomes handle this is that they also consider Decimal Points, Commas and Plus Signs to be 'numeric!'

This code will warn users if any characters except 0-9 or the Minus Sign (-) is entered...replacing Value2Test with the actual name of your Control, of course.

Private Sub Value2Test_KeyPress(KeyAscii As Integer)
If (KeyAscii > 47 And KeyAscii < 58) Or (KeyAscii = 8) Or (KeyAscii = 9) Or (KeyAscii = 45) Then
      KeyAscii = KeyAscii
      Else
      MsgBox ("You Must Only Enter Digits 0 9 or Minus Sign!")
      KeyAscii = 0
   End If
End Sub

but one question you didn't address is can the Minus Sign appear anywhere within the data...or does it have to be at the beginning or at the end of the digits?

Linq ;0)>

 

[freuzo]

theDBguy
Yes I thought about it. But I didn't succeed to have exactly what I wanted.

plog
Yes I know about the numeric type but numeric type but I got nothing with it.

missinglinq
The format : some numbers (can go from one to five) before the sign, then 2 numbers, the sign again, and 2 numbers at the end. I didn't test your code yet though.

 

[theDBguy]

theDBguy
Yes I thought about it. But I didn't succeed to have exactly what I wanted.

Hi. Maybe if you can show us what you tried, we can help you fix it. Just a thought...

 

[missinglinq]

missinglinq
The format : some numbers (can go from one to five) before the sign, then 2 numbers, the sign again, and 2 numbers at the end. I didn't test your code yet though.

Well, the code I gave you will only allow 0-9 and the Minus Sign.

Linq ;0)>

 

[freuzo]

Hi. Maybe if you can show us what you tried, we can help you fix it. Just a thought...

I was talking about something like : 1-20-05 or 9864-06-19 or 345-13-54.

Thanks to missinglinq for the code. I didn't use it yet (busy Sunday) but I have no doubt that it will work.

 

[freuzo]

The great thing here is that you guys always have a solution for problems.
Thank you all guys.

 

[Gasman]

I was talking about something like : 1-20-05 or 9864-06-19 or 345-13-54.

Thanks to missinglinq for the code. I didn't use it yet (busy Sunday) but I have no doubt that it will work.

I think you would have to create your own function?
I took this thread to be a single digit and a sign from the thread heading. Now it has evolved to a certain set of patterns with multiple digits.
Criteria is not clear as to how many digits could be in each portion?

 

[plog]

1-20-05 or 9864-06-19 or 345-13-54.

Perhaps its just a coincidence of your sample data, but will every input value be a number a dash a number a dash and another number? That is, is that really 1 piece of data or does each distinct part have a meaning?

 

[Isaac]

Create a function that looks at Instr() vs. the .Text property of the control, and put it in the control's Change event

 

[Gasman]

Create a function that looks at Instr() vs. the .Text property of the control, and put it in the control's Change event

I think Plog is on the right track? This value is going to describe 3 separate entities, so no need for complicated valuation. Evaluate each in turn?

 

[arnelgp]

paste this function in a Module:

Public Function isValidInput(ByVal str As String) As Boolean
    Const maxLen As Integer = 11
    Dim ln As Integer
    Dim p As String, ret As Boolean
    ln = Len(str)
    If ln < 1 Or ln > maxLen Then
        Exit Function
    End If
    Select Case ln
        Case Is < 6
            p = "^\d{1,5}$"
        Case Is = 6
            p = "^\d{1,5}\-$"
        Case Is = 7
            p = "^\d{1,5}\-\d{1}$"
        Case Is = 8
            p = "^\d{1,5}\-\d{2}$"
        Case Is = 9
            p = "^\d{1,5}\-\d{2}\-$"
        Case Is = 10
            p = "^\d{1,5}\-\d{2}\-\d{1}$"
        Case Is = 11
            p = "^\d{1,5}\-\d{2}\-\d{2}$"
    End Select
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = p
        ret = .test(str)
    End With
    isValidInput = ret
End Function

add code to the Textbox's Change event:

Private Sub Text0_Change()
    Dim s As String
    s = Me.Text0.Text
    Debug.Print Len(s)
    If Len(s) < 1 Then
        Exit Sub
    End If
    If Not isValidInput(s) Then
        MsgBox "Invalid input"
        SendKeys "{BS}"
    End If
    
End Sub

 

[freuzo]

Hi,
Just to close the thread. I choosen missinglinq's code because it is smaller and easy to understand. I even edit it a little.
Thank you all.

 

[Isaac]

Glad you got it sorted.