Check value against range, may include text. 3

[Emblem1]

Here is a good one!

I have an unbound form with textbox txtSerial. There are 2 labels - lblRecallYes and lblRecallNo, and a button cmdRecallCheck.

I want to be able to put any number in the box and check it againt the range of 0607001761 and 070413756, inclusive. The serial numbers entered into txtSerial may or may not have a preceding "0", "00" or "000" in the serial number. However the serial number can also be fewer in digit count, such as 921136. There may also be an occasion where there is text at the end, such as '921136-f' or '921136-failed' or '921136failed' or '921136FAILED', etc..

After the user enters the serial number and click the button. I want it to ignore any text on the end, but check the numeric value only.

The serial numbers entered into txtSerial may or may not have a preceding "0", "00" or "000" in the serial number.

There is no 'Format' to the txtbox under properties.

I have no idea how to attack the serial number checking in vba. I want to use the code under the cmdRecallCheck_Click event. If the serial number is part of the range, then lblRecallYes visible prperty is yes, otherwise show lblRecallNo.

Thanks!

 

[randy700]

You can strip out the number portion with a function similar to this.

Public Function GetNumericValue()
    Dim x As String
    Dim y As Integer
    Dim z As Long
    x = "00921136FAILED"
    For y = 1 To Len(x)
        If IsNumeric(Mid(x, y, 1)) Then
            z = z & Mid(x, y, 1)
        End If
    Next y
End Function

Then, it seems, you just need to determine if the value is within the stated range.

If z >= [i]LowValue[/i] and z <= [i]HighValue[/i] Then
    MsgBox z & " is between " & [i]LowValue[/i] & " and " & [i]HighValue[/i]
End If

Randy

 

[Emblem1]

Thanks! It works great.

However I want to make it dummy proof and limit the value that can be entered. I tried using the input mask but came up with some weird mask to conform to both the alpha and numeric characters, which didn't work well anyway.

If you enter something like 9999999999, you get a Runtime Error '6' OverFlow error and it highlights the code of z = z & Mid(x, y, 1). Can it be set to catch anything above 900000000 before it reaches that part of the code and give the error?

 

[PHV]

Why not simply use the Val function ?
? Val("00921136FAILED")
921136

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Emblem1]

If I can I would like do a verification on the serial number after stripping the text to see if it is a valid serial number or not. I think that Randy's code will work as is if I can verify a proper serial number entry before checking it against the recall range.

Serial numbers work like this:

The first 2 digits rep. the year, so the validation should only allow the first 2 numbers be anywhere from 86 to 08, but check on the current year and update the latter set as needed.

The next 1 or 2 the month (1 -12) in units made before year '99, and the next 2 the month (01-12) in year 99 and after.

Then the next 1 to 5 digits is the unit number.

If I can get vba code to validate wether the serial number entered is valid or not, I can also use it on another form for an table I have that has an inventory of these numbers, instead of just checking the recall status on the unbound form.

Here are some sample serial numbers.
9009-453 stored as 9009453
9206-496 stored as 9206496
911-154 stored as 911154
9211-61 stored as 921161
000409439
03076518
031124585-f
061200322

By 'stored as" I am referring as to how I keep the numbers in my serial number table. The last 4 are stored in the table as shown.

I have no idea to modify the code from Randy to include serial number validation, let alone use Val funtion, but here is what I did with what he gave me.

Private Sub cmdCheck_Click()

Dim x As String
Dim y As Integer
Dim z As Long

x = Me.txtSerial

For y = 1 To Len(x)
If IsNumeric(Mid(x, y, 1)) Then
z = z & Mid(x, y, 1)
End If
Next y

If z <= 0 Then
MsgBox "Please enter a serial number.", vbOKOnly + vbCritical, "NO SERIAL NUMBER"
txtSerial.SetFocus
Exit Sub
ElseIf z >= 60700171 And z <= 70413756 Then
Me.lblRecallUnit.Visible = True
Me.txtSerial.ForeColor = 255
MsgBox "Serial Number '" & x & "' is a recall unit!" & vbCrLf _
& "Be sure that recall repairs have been made before using in field!", vbOKOnly + vbCritical, "RECALLED UNIT!"
Else: Me.lblUnitOK.Visible = True
Me.txtSerial.ForeColor = 32768
MsgBox "Serial Number '" & x & "' does not need to be sent in for recall.", vbOKOnly + vbInformation, "UNIT OK"
End If
End Sub

Sorry, but I basically need it spelled out for me. But I am learning tons from the website. Keep up the great work!

 

[TheAceMan1]

How are ya Emblem1 . . .

Agree with [blue]PHV[/blue]. . . Have a look at the [blue]VAL[/blue] function. It fits like a glove! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Emblem1]

Success.

I did a little research and came up with this.

Thanks to all!

Here is the final code. If you have suggestions let me know!

Private Sub cmdCheck_Click()

Dim d As String 'used for date
Dim t As String 'used for 0's in the serial number prefix
Dim u As String 'used for digits after hyphen if hyphen is used
Dim v As String 'used for digits before hyphen if hyphen is used
Dim w As String 'numeric only serial number
Dim x As String 'raw serial number
Dim y As Integer
Dim z As Long

d = Date
x = Me.txtSerial
w = Val(x)

'check for hyphen in digit position 4
If Mid(x, 4, 1) = "-" Then
u = Mid(x, 5) 'select remaining digits after hyphen
v = Val(u) 'select digits before hyphen
w = w & v 'put it back together with hyphen removed

'check for hyphen in digit position 5
ElseIf Mid(x, 5, 1) = "-" Then
u = Mid(x, 6) 'select remaining digits after hyphen
v = Val(u) 'select digits before hyphen
w = w & v 'put it back together with hyphen removed
End If

'check for 0's in prefix and put them back in temporarily
If Left(x, 3) = "000" Then
w = "000" & w
ElseIf Left(x, 2) = "00" Then
w = "00" & w
ElseIf Left(x, 1) = "0" Then
w = "0" & w
End If

'Check for proper length serial number 6 to 9 digits long
If Len(w) < 6 Or Len(w) > 9 Then
MsgBox "Invalid Serial Number." & vbCrLf _
& "Serial Number must be between 6 and 9 digits long." & vbCrLf _
& "Hyphens can only be used in the serial after the 3 or 4 number, and at the end of the number." & vbCrLf _
& "Examples:" & vbCrLf _
& "904-123 or 904123" & vbCrLf _
& "9009-123 or 9009123" & vbCrLf _
& "9009-1234-anytext" & vbCrLf _
& "031124585-f", vbOKOnly + vbCritical
txtSerial.SetFocus
Exit Sub

'Verify first 2 numbers are not higher than this years last 2 digits or lower than "87" (1987 selected at random).
If Left(w, 2) > Right(d, 2) Then
If Left(w, 2) < 87 Then '87 can be changed to suit your earliest known hardware
MsgBox "Invalid Serial Number." & vbCrLf _
& "First 2 Serial Numbers Must be higher than 87 and lower than " & Right(d, 2) & ".", vbOKOnly + vbCritical
txtSerial.SetFocus '87 noted here in msgbox
Exit Sub
End If
End If
Else:
w = Val(x) 'take the prefix 0's back out in case they are there again
For y = 1 To Len(x)
If IsNumeric(Mid(x, y, 1)) Then
z = z & Mid(x, y, 1)
End If
Next y
End If

If z <= 0 Then
MsgBox "Please enter a serial number.", vbOKOnly + vbCritical, "NO SERIAL NUMBER"
txtSerial.SetFocus
Exit Sub
ElseIf z >= 60700171 And z <= 70413756 Then
Me.lblRecallUnit.Visible = True
Me.txtSerial.ForeColor = 255
MsgBox "Serial Number '" & x & "' is a recall unit!" & vbCrLf _
& "Be sure that recall repairs have been made before using in field!", vbOKOnly + vbCritical, "RECALLED UNIT!"
Else: Me.lblUnitOK.Visible = True
Me.txtSerial.ForeColor = 32768
MsgBox "Serial Number '" & x & "' does not need to be sent in for recall.", vbOKOnly + vbInformation, "UNIT OK"
End If
End Sub

 

[Emblem1]

However I have one last question.

I am still learning vba and think I get this part but want to be sure:

For y = 1 To Len(x)
If IsNumeric(Mid(x, y, 1)) Then
z = z & Mid(x, y, 1)
End If
Next y

For each time the value of y is 'less than' or finally reaching 'equal to' the number of digits in the serial number, it's looping thru the length of the serial number checking for numerals, and once it reaches the end of numerals, the if/then goes false and continues on thru the code?

Thanks.