compare fields and validate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to validate one field against another on a form. I have an email address and a Y/N field asking if they want to receive a newsletter by email. The validation is: If e-newsletter is "Y" then there must be an email address provided. I tried this but it doesn't seem to like me testing for Null in the email address field. I put this validation into the BeforeUpdate event.
Any ideas?
 
Simplest way to enforce that rule is to create a validation rule on the
table. Access is than unable to save any record that does not meet the rule.

Steps:
1. Open your table in design view.

2. Open the Properties box (View menu).

3. Enter the expression below into the Validation Rule in the Properties
box:
([e-newsletter] = False) OR ([email address] Is Not Null)

Make sure that goes into the rule in the box, NOT the Validation Rule in the
lower pane of the table design window. (That one applies to one field, and
you need the one that applies to the table.)

If is also possible to use Form_BeforeUpdate event, but that's better only
when you want to give the user the choice to break the rule.

--
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.

John said:
I want to validate one field against another on a form. I have an email
Click to expand...
address and a Y/N field asking if they want to receive a newsletter by
email. The validation is: If e-newsletter is "Y" then there must be an email
address provided. I tried this but it doesn't seem to like me testing for
Null in the email address field. I put this validation into the BeforeUpdate
event.
 
Agree with Allen, but to clear you mind on what's probably going wrong with
your code, it seems from your description that you are using something like

If Me.email_address = Null Then
......
This will always result in False because Null isn't the same as empty. Null
is Unknown. So you are asking if your email_address = Unknown
Try something like:

If IsNull(Me.email_address) Then
.....
or

If Not Nz(Me.email_address,"")<>"" Then
.....

--
Reggie

----------
Allen Browne said:
Simplest way to enforce that rule is to create a validation rule on the
table. Access is than unable to save any record that does not meet the rule.

Steps:
1. Open your table in design view.

2. Open the Properties box (View menu).

3. Enter the expression below into the Validation Rule in the Properties
box:
([e-newsletter] = False) OR ([email address] Is Not Null)

Make sure that goes into the rule in the box, NOT the Validation Rule in the
lower pane of the table design window. (That one applies to one field, and
you need the one that applies to the table.)

If is also possible to use Form_BeforeUpdate event, but that's better only
when you want to give the user the choice to break the rule.

--
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.

John said:
I want to validate one field against another on a form. I have an email
Click to expand...
address and a Y/N field asking if they want to receive a newsletter by
email. The validation is: If e-newsletter is "Y" then there must be an email
address provided. I tried this but it doesn't seem to like me testing for
Null in the email address field. I put this validation into the BeforeUpdate
event.
Any ideas?
Click to expand...
Click to expand...
 
Back
Top