Validating E-mail addresses

[Bob Richardson]

This seems to be a common problem. I placed the following code in a form
where most users will be entering e-mail addresses. I'm sure it can be
improved on, especially in the area of "invalid characters." But this
approach won't work if data is entered on a datasheet. Is there a way to
code something in the "Validation Rule" area in order to trap these errors?

Private Sub Email_BeforeUpdate(Cancel As Integer)
If Me!EmailAdr Like "*[ !$%^&*()]*" Then
MsgBox "Invalid characters in EMail address", vbOKOnly
Cancel = True
End If

If Not (Me!EmailAdr Like "*@*.*") Then
MsgBox "EMail should be in the format na...@server.domain", vbOKOnly
Cancel = True
End If
End Sub

[Van T. Dinh]

Have you tried something like:

(Like "*@*.*" And Not Like "*[ !$%^&*()]*") Or Is Null

in the Validation Rule of the Field in the DesignView of
the Table?

HTH
Van T. Dinh
MVP (Access)

>.
>

[Bob Richardson]

Seems to work perfectly. Thanks a ton.

When I applied it to my existing DB, I got one error message indicating that
there was a problem with an existing e-mail address. Since I got just one
message, I presume there was only one bad e-mail in the file (only 1700
records). Is that correct?

Is there a way to find out which record had the error? What happened to the
e-mail address for that record?

"Van T. Dinh" <VanThi...@discussions.microsoft.com> wrote in message
news:0efc01c4ab4a$91a92ed0$a601...@phx.gbl...

[Van T. Dinh]

There may be more than one Record that has bad e-mail address.

I guess you can use a select Query with the negation of the validation rule
as the selection criteria to select Records that have bad e-mail addresses.

The criteria should be:

(Not Like "*@*.*") Or (Like "*[ !$%^&*()]*")

--

HTH
Van T. Dinh
MVP (Access)

"Bob Richardson" <bobr at whidbey dot com> wrote in message
news:066dne6iNNe...@whidbeytel.com...

[Pats@discussions.microsoft.com Mickey Pats]

I have a field where my users will need to enter the Last Name, First Name.
What validation rule can i put in place to ensure that enter Last Name, First
Name.
I want the values in this field to only have char and not numbers. Also no
special Char.

Field: ( Pats, Mickey)

[Rick B]

I doubt you can. You can very that it contains a comma, but that is about
it.

What about "Smith, Bobby Jo"

Or "Thorne Smith, Courtney"

Or "Johnson, J.R."

Lots of variables with names.

About the best you could do is verify that there is a comma and verify that
the length is at least X characters. I knew a guy who's last name was "O".
The airline computers required a length of two characters or more, so all
his tickets said "OO" :-)

Rick B

"Mickey Pats" <Mickey Pa...@discussions.microsoft.com> wrote in message
news:AEA29935-7EA5-4CAC...@microsoft.com...

[Rick B]

note, you can use the input mask to eliminate special characters and
numbers, but then you run into that comma.

If it were me, I'd have two separate fields. One for first, and one for
last. You will stull not be able to get rid of special characters.
O'Conner

Rick B

"Mickey Pats" <Mickey Pa...@discussions.microsoft.com> wrote in message
news:AEA29935-7EA5-4CAC...@microsoft.com...

[Pats@discussions.microsoft.com Mickey Pats]

Rick my friend suggested this but any code suggestions to making sure they
have a comma in the field. Like here is what my friend said to try.

=InStr([contact response 1].[Value],",")>0 And InStr([contact response
1].[Value],",")<Len([contact response 1].[Value])

Contact response is my field for Last name and first name.