Validating Email Address

I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I combine
them with an 'OR' clause I am missing a parenthesis or comma somewhere - can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

On Wed, 30 Dec 2009 19:17:01 -0800, Steve Stad
wrote:

I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I combine
them with an 'OR' clause I am missing a parenthesis or comma somewhere - can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

Unlike Excel, which has an OR() function, Access uses OR as an operator - very
much like + for addition or - for subtraction. Try

IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0 OR
InStr([EMPLOYEE_EMAIL_ADDRESS],"@")=0,"checkemail","")

This of course does not guarantee a valid address but it should winnow a lot
of the problems.
--

John W. Vinson [MVP]

How about adding a validation rule on the field in table design?

Something along these lines:
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))

That example requires at least one character, @, at least one character,
dot, at least one character. Space, comma, and semicolon are not permitted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Steve Stad" wrote in message
...
I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I
combine
them with an 'OR' clause I am missing a parenthesis or comma somewhere -
can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

if you really want to validate the email, you need to send an email and then
use automation to check your mailbox for rejects.

----
HTH
Dale



"Steve Stad" wrote:

I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I combine
them with an 'OR' clause I am missing a parenthesis or comma somewhere - can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

Thanks John/Allen/Dale,

All your advise was great. I think validating in the table desing mode is
most effecient.

"Allen Browne" wrote:

How about adding a validation rule on the field in table design?

Something along these lines:
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))

That example requires at least one character, @, at least one character,
dot, at least one character. Space, comma, and semicolon are not permitted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Steve Stad" wrote in message
...
I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I
combine
them with an 'OR' clause I am missing a parenthesis or comma somewhere -
can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

.

I don't disagree, but until you actually send an email to the address, you
have only validated the appearance of the email address, not that the
address actually works or is the email assigned to the person who is
accessing it. Actually, my method doesn't actually validate that the email
address provided belongs to the person, only that it is not rejected by the
email servers.

Why do you think so many web sites send you a validation email, with a
hyperlink that takes you back to another page on their site?

Dale

"Steve Stad" wrote in message
...
Thanks John/Allen/Dale,

All your advise was great. I think validating in the table desing mode is
most effecient.

"Allen Browne" wrote:

How about adding a validation rule on the field in table design?

Something along these lines:
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))

That example requires at least one character, @, at least one character,
dot, at least one character. Space, comma, and semicolon are not
permitted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Steve Stad" wrote in message
...
I want to verify/validate user has input a good email address so I am
checking for the '@' sign and a period in the email string. Is there a
better way? The two iif statements work fine separately but when I
combine
them with an 'OR' clause I am missing a parenthesis or comma
somewhere -
can
you give me a clue?
dblchk:
iIf(or(InStr([EMPLOYEE_EMAIL_ADDRESS],".")=0,IIf(InStr([EMPLOYEE_EMAIL_ADDRESS],"@"))=0,"checkemail",""))

Thanks
Steve

.