On Mar 28, 11:29 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:

I think being ANSI mode neutral is a good thing (e.g. to enforce data
rules for both ADO and DAO) and the validation could be a little more
involved e.g.

([email_address] LIKE '%_@_%._%' OR [email_address] LIKE '*?@?*.?*')
AND [email_address] <> '%_@_%._%'
AND [email_address] <> '*?@?*.?*'
AND [email_address] NOT LIKE '%[!0-9A-Z''._@-]%'
AND [email_address] NOT LIKE '*[!0-9A-Z''._@-]*'
AND LEN(MID([email_address], 1, INSTR(1, [email_address], '@') - 1))
BETWEEN 1 AND 127
AND LEN(MID([email_address], INSTR(1, [email_address], '@') + 1))
BETWEEN 1 AND 127
AND INSTR(INSTR(1, [email_address], '@') + 1, [email_address], '@') =
0

Actually, rather than having one big Validation Rule, I'd have several
smaller rules implemented as CHECK constraints, each with a meaningful
name that would appear in the error description, to be able to give
more meaningful feedback to the user e.g.

Sub ValidateEmail()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
 .Create _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & _
     Environ$("temp") & "\DropMe.mdb"
 With .ActiveConnection
   .Execute _
   "CREATE TABLE Test ( " & vbCr & "email_address" & _
   " VARCHAR(255) NOT NULL, " & vbCr & "CONSTRAINT" & _
   " email__basic_pattern" & vbCr & "CHECK (" & vbCr & _
   " (email_address" & _
   " LIKE '%_@_%._%' OR email_address" & _
   " LIKE '*?@?*.?*') " & vbCr & "AND email_address" & _
   " <> '%_@_%._%' " & vbCr & "AND email_address" & _
   " <> '*?@?*.?*'" & vbCr & "), " & vbCr & "CONSTRAINT" & _
   " email__legal_characters" & vbCr & "CHECK" & _
   " (" & vbCr & "email_address NOT LIKE '%[!0-9A-Z''._@-]%'" & _
   " " & vbCr & "AND email_address NOT LIKE" & _
   " '*[!0-9A-Z''._@-]*'" & vbCr & ")," & _
   " " & vbCr & "CONSTRAINT email__mailbox_name_length" & vbCr & _
   " CHECK (" & vbCr & "LEN(MID(email_address, 1, INSTR(1," & _
   " email_address, '@') - 1)) BETWEEN" & _
   " 1 AND 127" & vbCr & ")," & vbCr & _
   " CONSTRAINT email__domain_name_length" & vbCr & "CHECK" & _
   " (" & vbCr & "LEN(MID(email_address, INSTR(1," & _
   " email_address, '@') + 1)) BETWEEN" & _
   " 1 AND 127" & vbCr & ")," & vbCr & _
   " CONSTRAINT email__one_commerical_at" & vbCr & "CHECK" & _
   " (" & vbCr & "INSTR(INSTR(1, email_address," & _
   " '@') + 1, email_address, '@')" & _
   " = 0" & vbCr & ")" & vbCr & ");"

   On Error Resume Next
   ' Try adding illegal value (two @'s)
   .Execute _
   "INSERT INTO Test (email_address) VALUES ('a@b.c@m');"
   If InStr(Err.Description, "email__one_commerical_at") > 0 Then
     MsgBox "A valid email may only have one '@' character."
   End If
   On Error GoTo 0
 End With
 Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--