I am an access novice and would like to create avalidationrule for an
email address in Access 2000.
Try:
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))
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 ('(e-mail address removed)@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.
--
