Validation rule for email field

[Guest]

I want to have a validation rule that enforces the use of "@" for the "email"
field of a table.
How is the validaton rule created? I am not very proficient in code, and
relativelly new to Access, but I have checked the Expression Builder and I
could not find an expression that will allow typing and undefinite amount of
caracters before and after the '@" symbol.
I will appreciate if someone could give me an indication of how this is
written.

Thank you for your help!
Rocio

 

[Jeff Boyce]

Rocio

Look up the InStr() function in HELP. The basic approach is something like:

If InStr([YourControlName],"@")>0 Then
'do what you want to do if there's an "@"
Else
'do what you want to do if there isn't
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff, thanks for giving me a hint in the right direction. I will lookup that
function in HELP. but right from what I read in your directions I wouldn't
know what to put in [YourControlName] as this will depend on the name of the
person inputting the email. If you refer here to the field name, then the
question is if I should be placing the piece of code in the field's
validation rule or in the table property validation's rule?
For now, I will find out in the HELP... and will appreciate if you can
expand a bit on these doubts. THANKS!

Rocio

Look up the InStr() function in HELP. The basic approach is something like:

If InStr([YourControlName],"@")>0 Then
'do what you want to do if there's an "@"
Else
'do what you want to do if there isn't
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to have a validation rule that enforces the use of "@" for the
"email"
field of a table.
How is the validaton rule created? I am not very proficient in code, and
relativelly new to Access, but I have checked the Expression Builder and I
could not find an expression that will allow typing and undefinite amount
of
caracters before and after the '@" symbol.
I will appreciate if someone could give me an indication of how this is
written.

Thank you for your help!
Rocio

 

[Jeff Boyce]

As a general approach, you'll have to decide if you want to evaluate the
entry in the field/control itself, or wait until the user fills in the
entire form. The former is friendlier, as the user finds out right away
that something's not complete. The latter is easier for you, because you
can put ALL the validation checking into the form's BeforeUpdate event, and
cancel if everything doesn't pass.

You'll need to be looking into using form events if you want this kind of
control ... I don't believe the If Then Else statement can work inside a
table field's validation rule.

[YourControlName] was my shorthand for you using a form and a control. The
form will have a control into which the user enters something, hopefully an
email address. But the NAME of that control is what your If Then Else
statement needs to look at.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, thanks for giving me a hint in the right direction. I will lookup
that
function in HELP. but right from what I read in your directions I wouldn't
know what to put in [YourControlName] as this will depend on the name of
the
person inputting the email. If you refer here to the field name, then the
question is if I should be placing the piece of code in the field's
validation rule or in the table property validation's rule?
For now, I will find out in the HELP... and will appreciate if you can
expand a bit on these doubts. THANKS!

Rocio

Look up the InStr() function in HELP. The basic approach is something
like:

If InStr([YourControlName],"@")>0 Then
'do what you want to do if there's an "@"
Else
'do what you want to do if there isn't
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to have a validation rule that enforces the use of "@" for the
"email"
field of a table.
How is the validaton rule created? I am not very proficient in code,
and
relativelly new to Access, but I have checked the Expression Builder
and I
could not find an expression that will allow typing and undefinite
amount
of
caracters before and after the '@" symbol.
I will appreciate if someone could give me an indication of how this is
written.

Thank you for your help!
Rocio

 

[Allen Browne]

Rocio, if you did want to use a Validation Rule on the field in the table,
you could try something like this:

Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*"))

That requires at least 1 character, @, at least one character, dot, at least
one character.

Put any illegal characters inside the square brackets.

 

[Jamie Collins]

I want to have a validation rule that enforces the use of "@" for the "email"
field of a table.

You need validation rules (CHECK constraints) in the database to ensure
only valid data gets in.

Rather than a complex rule that covers all possibilities (regex style),
write several expressions that each tests a single rule. Of course, you
first need to investigate what the business rules are e.g. your

undefinite amount of
caracters before and after the '@" symbol

may not be correct; the number of characters may be variable but not
unlimited e.g.

http://www.govtalk.gov.uk/gdsc/schemaHtml/CommonSimpleTypes-v1-3-xsd-EmailAddressType.htm

specifies between 1 and 127 characters.

Here some suggestions (I'll break my own rule and post ANSI mode only
wildcard characters <g>):

CREATE TABLE ClientContacts (Email VARCHAR(255) NOT NULL);

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__pattern
CHECK (Email LIKE '%[A-Z]%@%[A-Z]%.%[A-Z]%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__characters
CHECK (Email NOT LIKE '%[!0-9A-Z''.@_-]%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__one_commercial_at
CHECK (Email NOT LIKE '%@%@%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__mailbox_name_length
CHECK ((INSTR(1, Email, '@') - 1) BETWEEN 1 AND 127)

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__domain_name_length
CHECK (LEN(Email) - INSTR(1, Email, '@') BETWEEN 3 AND 127)

Jamie.

--

 

[Guest]

Jeff, Alan and Jamie,
Thank you very much for taking the time to respond to my post. I am sure I
have more than one response for my question between the three of you. Some
parts go over my head very fast, but others point to the direction I need and
even further... give me some ideas for solutions to possible scenarios.
Thank you again!

I want to have a validation rule that enforces the use of "@" for the "email"
field of a table.

You need validation rules (CHECK constraints) in the database to ensure
only valid data gets in.

Rather than a complex rule that covers all possibilities (regex style),
write several expressions that each tests a single rule. Of course, you
first need to investigate what the business rules are e.g. your

undefinite amount of
caracters before and after the '@" symbol

may not be correct; the number of characters may be variable but not
unlimited e.g.

http://www.govtalk.gov.uk/gdsc/schemaHtml/CommonSimpleTypes-v1-3-xsd-EmailAddressType.htm

specifies between 1 and 127 characters.

Here some suggestions (I'll break my own rule and post ANSI mode only
wildcard characters <g>):

CREATE TABLE ClientContacts (Email VARCHAR(255) NOT NULL);

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__pattern
CHECK (Email LIKE '%[A-Z]%@%[A-Z]%.%[A-Z]%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__characters
CHECK (Email NOT LIKE '%[!0-9A-Z''.@_-]%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__one_commercial_at
CHECK (Email NOT LIKE '%@%@%')

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__mailbox_name_length
CHECK ((INSTR(1, Email, '@') - 1) BETWEEN 1 AND 127)

ALTER TABLE ClientContacts ADD CONSTRAINT
ClientContacts__Email__domain_name_length
CHECK (LEN(Email) - INSTR(1, Email, '@') BETWEEN 3 AND 127)

Jamie.