Validating a Field

[Guest]

Hello all,

One of the fields on my form is a book code. A book code has 7 digits (the
first digit is alpha and the last 6 digits are numeric), and it is a text
field.

When a user enters a book code on the form, I want the field to validate to
make sure that it is 7 digits in total and the structure I described above.
If the validation fails, it will prompt the user a message. Currently, when
a user enters only 6 digits in total, it accepts the value.

Please help. Thanks.

 

[B. Edwards]

You could use an input mask:

L000000

 

[Douglas J. Steele]

Try the following for your text box's BeforeUpdate event:

Private Sub Text1_BeforeUpdate(Cancel As Integer)

If Len(Me!Text1) <> 7 Then
MsgBox "Must be 7 characters."
Cancel = True
ElseIf UCase(Left(Me!Text1, 1) < "A" _
Or UCase(Left(Me!Text1, 1) > "Z" Then
MsgBox "Must start with a letter"
Cancel = True
ElseIf CLng(Mid(Me!Text1, 2)) <> Val(Mid(Me!Text1, 2)) Then
MsgBox "Must contain 6 digits
Cancel = True
End If

End Sub

 

[missinglinq via AccessMonster.com]

That dog just won’t hunt, Doug!

ElseIf UCase(Left(Me!Text1, 1) < "A" Or UCase(Left(Me!Text1, 1) > "Z" Then

needs some extra parentheses

ElseIf UCase(Left(Me!Text1, 1)) < "A" Or UCase(Left(Me!Text1, 1)) > "Z" Then

and

ElseIf CLng(Mid(Me!Text0, 2)) <> Val(Mid(Me!Text0, 2)) Then

throws an Error 13 Type Mismatch if an alpha character is entered

How about this:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
If Len(Text1) <> 7 Then
MsgBox "Book Code Must Be 1 Letter Followed By 6 Digits!"
Cancel = True
End If
If Not ((Asc(Left(Text1, 1)) >= 65) And (Asc(Left(Text1, 1)) <= 90))_
And Not ((Asc(Left(Text1, 1)) >= 97) And (Asc(Left(Text1, 1)) <= 122)) Then
MsgBox "The First Character of the Book Code Must Be a Letter"
Cancel = True
End If
If Not IsNumeric(Right(Text1, 6)) Or InStr(Text1, ".") > 1_
Or InStr(Text1, "+") > 1 Or InStr(Text1, "-") > 1 Then
MsgBox "Last 6 Characters of the Book Code Must Be Numeric!"
Cancel = True
End If

End Sub

Try the following for your text box's BeforeUpdate event:

Private Sub Text1_BeforeUpdate(Cancel As Integer)

If Len(Me!Text1) <> 7 Then
MsgBox "Must be 7 characters."
Cancel = True
ElseIf UCase(Left(Me!Text1, 1) < "A" _
Or UCase(Left(Me!Text1, 1) > "Z" Then
MsgBox "Must start with a letter"
Cancel = True
ElseIf CLng(Mid(Me!Text1, 2)) <> Val(Mid(Me!Text1, 2)) Then
MsgBox "Must contain 6 digits
Cancel = True
End If

End Sub

Hello all,

[quoted text clipped - 12 lines]

Please help. Thanks.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Douglas J. Steele]

Yeah, you're right. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That dog just won't hunt, Doug!

ElseIf UCase(Left(Me!Text1, 1) < "A" Or UCase(Left(Me!Text1, 1) > "Z" Then

needs some extra parentheses

ElseIf UCase(Left(Me!Text1, 1)) < "A" Or UCase(Left(Me!Text1, 1)) > "Z"
Then

and

ElseIf CLng(Mid(Me!Text0, 2)) <> Val(Mid(Me!Text0, 2)) Then

throws an Error 13 Type Mismatch if an alpha character is entered

How about this:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
If Len(Text1) <> 7 Then
MsgBox "Book Code Must Be 1 Letter Followed By 6 Digits!"
Cancel = True
End If
If Not ((Asc(Left(Text1, 1)) >= 65) And (Asc(Left(Text1, 1)) <= 90))_
And Not ((Asc(Left(Text1, 1)) >= 97) And (Asc(Left(Text1, 1)) <= 122))
Then
MsgBox "The First Character of the Book Code Must Be a Letter"
Cancel = True
End If
If Not IsNumeric(Right(Text1, 6)) Or InStr(Text1, ".") > 1_
Or InStr(Text1, "+") > 1 Or InStr(Text1, "-") > 1 Then
MsgBox "Last 6 Characters of the Book Code Must Be Numeric!"
Cancel = True
End If

End Sub

Try the following for your text box's BeforeUpdate event:

Private Sub Text1_BeforeUpdate(Cancel As Integer)

If Len(Me!Text1) <> 7 Then
MsgBox "Must be 7 characters."
Cancel = True
ElseIf UCase(Left(Me!Text1, 1) < "A" _
Or UCase(Left(Me!Text1, 1) > "Z" Then
MsgBox "Must start with a letter"
Cancel = True
ElseIf CLng(Mid(Me!Text1, 2)) <> Val(Mid(Me!Text1, 2)) Then
MsgBox "Must contain 6 digits
Cancel = True
End If

End Sub

Hello all,

[quoted text clipped - 12 lines]

Please help. Thanks.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Hi guys,

Thank you very much for your helps. I hope you guys can help me one more
thing. I see a problem when the user space-bars through the book code and
when the user puts zero for the last 6 digits (e.g. "A000000").

Can you guys help me with the code for these situations?

Thanks.

Yeah, you're right. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That dog just won't hunt, Doug!

ElseIf UCase(Left(Me!Text1, 1) < "A" Or UCase(Left(Me!Text1, 1) > "Z" Then

needs some extra parentheses

ElseIf UCase(Left(Me!Text1, 1)) < "A" Or UCase(Left(Me!Text1, 1)) > "Z"
Then

and

ElseIf CLng(Mid(Me!Text0, 2)) <> Val(Mid(Me!Text0, 2)) Then

throws an Error 13 Type Mismatch if an alpha character is entered

How about this:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
If Len(Text1) <> 7 Then
MsgBox "Book Code Must Be 1 Letter Followed By 6 Digits!"
Cancel = True
End If
If Not ((Asc(Left(Text1, 1)) >= 65) And (Asc(Left(Text1, 1)) <= 90))_
And Not ((Asc(Left(Text1, 1)) >= 97) And (Asc(Left(Text1, 1)) <= 122))
Then
MsgBox "The First Character of the Book Code Must Be a Letter"
Cancel = True
End If
If Not IsNumeric(Right(Text1, 6)) Or InStr(Text1, ".") > 1_
Or InStr(Text1, "+") > 1 Or InStr(Text1, "-") > 1 Then
MsgBox "Last 6 Characters of the Book Code Must Be Numeric!"
Cancel = True
End If

End Sub

Try the following for your text box's BeforeUpdate event:

Private Sub Text1_BeforeUpdate(Cancel As Integer)

If Len(Me!Text1) <> 7 Then
MsgBox "Must be 7 characters."
Cancel = True
ElseIf UCase(Left(Me!Text1, 1) < "A" _
Or UCase(Left(Me!Text1, 1) > "Z" Then
MsgBox "Must start with a letter"
Cancel = True
ElseIf CLng(Mid(Me!Text1, 2)) <> Val(Mid(Me!Text1, 2)) Then
MsgBox "Must contain 6 digits
Cancel = True
End If

End Sub

Hello all,

[quoted text clipped - 12 lines]

Please help. Thanks.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com