is not null with like

[kris2011]

Hi,
I have this validation rule:

Like "*@*.co.uk" Or Like "*@*.com" Or Like "*@*.net"

How do I stop it from allowing null values within the rule.....

(?) IS Not Null and Like "*@*.co.uk" Or Like "*@*.com" Or Like "*@*.net" doesn't work.

With thanks I don't want to write any code or use 'Required' as I want to use the validation text to write my custom error message.

Cheers in advance

 

[stopher]

Your validation rule will not include nulls. You would have to force it to include nulls like this:

Like "*@*.co.uk" Or Like "*@*.com" Or Like "*@*.net" Or Is Null

Maybe you could show some data where you are having a problem

 

[kris2011]

I want to allow (without the spaces obviously):

bob@ hotmail.com
bobby@ hotmail.co.uk
sid@ hogan.net
But there MUST be an email address entered so cannot leave blank

Basically I want this to work:

Is not null AND Like "*@*.co.uk" Or Like "*@*.com" Or Like "*@*.net"

but the logic is wrong.
This with some brackets thrown in?
Is not null AND Like "*@*.co.uk" Or IS Not Null and Like "*@*.com" Or IS Not Null and Like "*@*.net"

 

[David R]

So you will not allow emails from yahoo.ca or yale.edu?

Anyway yes, I think Is not null AND (Like... OR like ...) will work, but you'll have to play with syntax. http://office.microsoft.com/en-us/access-help/validation-rules-HA010285532.aspx should help.

 

[Pat Hartman]

The validation rule is not the place to address nulls. Use the Required property of the column and don't allow zero length strings. So required = yes, allowzls = no

 

[kris2011]

Thanks David but tried that and it doesn't work
Tried this too which also doesn't work:
(Is Not Null And Like "*@*.co.uk") Or (Is Not Null And Like "*@*.com")...

 

[kris2011]

Cheers Pat but no use either.
Works fine but I need a customised error message
(without resorting to VB, my students brains will explode)

 

[stopher]

Sounds like you are picking up spaces because the following should not include nulls:
Like "*@*.co.uk" Or Like "*@*.com" Or Like "*@*.net"

So maybe test for spaces.

 

[David R]

Did you read the webpage I linked to? It has a ton of sample syntax.