Validating a Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AccessHelp - 27 Jul 2007 19:44 GMT

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.

Reply to this Message

B. Edwards - 27 Jul 2007 21:11 GMT

You could use an input mask:

L000000

> Hello all,
>
[quoted text clipped - 12 lines]
>
> Please help. Thanks.

Reply to this Message

Douglas J. Steele - 27 Jul 2007 21:26 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello all,
>
[quoted text clipped - 12 lines]
>
> Please help. Thanks.

Reply to this Message

missinglinq - 27 Jul 2007 22:31 GMT

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:
>
[quoted text clipped - 19 lines]
>>
>> Please help. Thanks.

Signature

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

Answers/posts based on Access 2000

Reply to this Message

Douglas J. Steele - 28 Jul 2007 00:08 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> That dog just won't hunt, Doug!
>
[quoted text clipped - 55 lines]
>>>
>>> Please help. Thanks.

Reply to this Message

AccessHelp - 30 Jul 2007 23:20 GMT

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.
>
[quoted text clipped - 57 lines]
> >>>
> >>> Please help. Thanks.

Reply to this Message