You can't have a validation rule which requires a value and also allow
Nulls. I think you need to allow nulls in the email field, since you want to
be able to add a new row without an email address. Set Required=No and use
the field validation rule:
Is Null or (Like "?*@?*.?*")
So that allows nulls but prohibits a known invalid address. You can find
more complete validation expressions by searching on the web if you want,
but this is all I bother with.
Now you probably have some other condition under which you want to require
an email address. You can enforce that with a table validation rule, as long
as you have suitable logic. A field validation rule can only use that
particular field's data, but a table validation rule can use all the data
fields in the row. For example, if the only data you enter from your Not In
List function is the last name and first name, you could have a table
validation rule that says either all the other fields in the table are Null,
or the email is Not Null. That way anytime someone edits the row and adds
any data beyond the first and last names, the email will be required.
You can also do more with the Not In List function than just add the new row
automatically. You could open the Person form in dialog mode, pre-fill the
first and last names, and require the user to enter whatever data is
required before allowing them to save the row.
deb said:
This will not work for me. If I put the validation in the table, it
errors
within the "not in list".
If I put the validation in the form or in the field after update. it is
ignored unless a change is made to the field data. If the field is
changed
in any way the error will then appear.
After using the "Not in list" funtion the email field is blank. I need a
way to make this dirty so it will check the field for validation.
any ideas?
--
deb
Paul Shapiro said:
I don't find an input mask helpful for an email address, because the
allowable formats vary too much.
I've used this as the validation rule for email attributes in the table's
field properties when Nulls are allowed:
Is Null or (Like "?*@?*.?*")
Since you don't want nulls, you could change that to:
Is Not Null or (Like "?*@?*.?*")
but that might fail on your blank fields. I think the behavior varied a
bit
with different Access versions.
Maybe your original rule should use And instead of Or?:
Is Not Null And Like "*?@?*.?*" And (Not Like "*[ ,;]*")
:
I am trying to enter a validation rule on the form called fEmail to
my
field
called RecipientEmail. I do not want the field to be empty and
needs
to be
in the correct emeil format ???@???.???
I have in the validation rule...
Not IsNull([RecipientEmail]) Or ((Like "*?@?*.?*") And (Not Like "*[
,;]*"))
