IsNumeric

[Sherrise]

I have a TxtBox with a number in it. I want to have a validation. If blank a message comes up, if it is not a numeric number a message comes up. The first If statement works, but not the IsNumeric!
2014-151566

This is my coding. What am I doing wrong?

If Me.TxtCaseNumber.Value = "" Then
MsgBox "This field must be field in with a numeric #", vbCritical
Exit Sub
End If


If IsNumeric(Me.TxtCaseNumber & "e-0".Value) = False Then
MsgBox "Please enter a Numeric Number", vbCritical
Exit Sub
End If

 

[steve the fish]

So does a valid result always have a 4 digit year followed by a dash then another number

eg 2023-0001

so 20230001 would be invalid? You need to tell us what you want to check for.

 

[Sherrise]

The will always be be a dash after the first for numbers.
There could be a M in front of the first for numbers. It will never be a different Letter.
This would be valid result.
####-###### 2023-459489
or
M####-#### M2023-459489

Correct this would be in valid 20230001
Correct this would be in valid M20230001

 

[steve the fish]

Try this:

x = Replace(UCase(Me.TxtCaseNumber), "M", "")
If x Like "####-*" Then
    If IsNumeric(Split(x, "-")(1)) Then
        MsgBox "Please enter a Numeric Number", vbCritical
        Exit Sub
    End If
End If

 

[Sherrise]

Thank you. I will try it.!!!

 

[Sherrise]

I got a run-time error 13; Type mismatch
This part of the coding was highlighted when debug

x = Replace(UCase(Me.TxtCaseNumber), "M", "")

 

[Mario_R]

Hello. You can try the following:

TxtCaseNumber = UCase(TxtCaseNumber)
With CreateObject("VbScript.RegExp")
  .Global = True: .Pattern = "M?\d{4}-\d{1,}": Set mTest = .Execute(TxtCaseNumber)
End With
If mTest.Count = 0 Then MsgBox "Please enter a Numeric Number", vbCritical: Exit Sub
If mTest(0) <> TxtCaseNumber Then MsgBox "Please enter a Numeric Number", vbCritical: Exit Sub

 

[Peter_SSs]

Another regexp option that you could try

With CreateObject("VBScript.RegExp")
  .Pattern = "^M?\d{4}-\d+$"
  If Not .Test(Me.TxtCaseNumber.Value) Then MsgBox "Please enter a valid case number"
End With

 

[Peter_SSs]

BTW I would definitely avoid using IsNumeric for this sort of test as it will allow things that here you would definitely not want allowed as numeric. For example IsNumeric would return True for
"4e4"
"3,5,6$"
"23,,,,,.3"
etc

 

[Sherrise]

Good morning. I will try these this morning. Thank you so much!!!