Validation Rules

[action]

Can anybody tell me how to write a validation rule for an email field ensuring the @ symbol is included?

thanks

 

[dcx693]

I'm sure someone out there has written a complete function, but you can easily use the Instr function to look for the @ symbol in a string. Use it like this:
Instr(1,[field with the e-mail address],"@")
As long as the function doesn't return 0, you have a @ in there somewhere. From there, you should check that something is in front of the @. That's easy enough since that Instr function above will only return 1 if the @ character is the first one in the string. Then you need to check for some text after the ampersand that includes a period somewhere in there. Just use the Instr function again, and make sure the period occurs after the @ and that there are some characters betwen the @ and the period. Then finally check for text after the period.

 

[action]

Found some code but error occurs

I found some code that does it except I get an error. Worth noting that I can handle very basic code but thats it.

code is:
Private Sub TxtEmail_BeforeUpdate(Cancel As Integer)

'create a variable in memory
Dim MyString As String

'fill the variable with data from
'the email textbox on the current form
'note to self: "Me" = the current form
MyString = Me.TxtEmail.Value

'if the textbox is null or has a @ then
If IsNull(MyString) Or InStr(MyString, "@") Then
'that's fine -- so leave
Exit Sub
End If

'otherwise, hold everything
' prompt the user
MsgBox "There's no '@' in the text box", vbOKOnly, "Invalid Address"

'now cancel and put the cursor back in the offending textbox
DoCmd.CancelEvent

End Sub


This works great except when an existing address is deleted and not replaced.

Any suggestions anybody?

 

[jfgambit]

Do this is the table where the field resides:

Validation Rule: InStr(1,[EMAddress],"@")<>0
Validation Text: You must enter a valid E-Mail ID: Example: jsmith@hotmail.com
Allow Null Values: Yes


If a user does not enter a valid e-mail address with the "@" sign, they will be prompted with the Validation Text.