Data Mask for EMail Addressess

[dantebothermy]

I thought this was an easy question, but I can't find an answer anywhere.

How do I set up a data mask in Access for email addresses?

There are two parts I can't figure: First how to set up part of a mask to accept unlimited characters. Second, what character to use that will accept any character EXCEPT a space.

Please help. Thanks.


Dante

[ChipR]

I haven't found any way to do either of these things with an input mask. One alternative is to use the BeforeUpdate or AfterUpdate event of the text box to check the text entered and make sure it's valid.

[ADezii]

@dantebothermy
A single Line of Code in the KeyPress() Event of a Text Box on a Form should accomplish what you are looking for. Assuming a Text Box Name of txtEMailAddress, the following Code will allow an Unlimited Number of Characters up to the defined Field Size, but will NOT allow a Space:

Private Sub txtEMailAddress_KeyPress(KeyAscii As Integer)
  'Do not allow a Space!
  If KeyAscii = 32 Then KeyAscii = 0
End Sub

[ChipR]

Although this prevents users from typing spaces, they can still enter invalid information by ctrl+v pasting text with spaces.

[missinglinq]

Apart from not having a space, valid addresses also have to have a single ampersand (@) and a valid generic top-level domain on the end.

The first two items are easy to check, but the sticking point comes when trying to verify the last point, the valid host domain types, i.e. com, edu, org, etc. because the possibilities are almost endless, especially if you're dealing with government workers' addresses.

Here's a routine that checks for spaces, checks for a single ampersand and checks the most common generic top-level domains, such as .com, .org, .edu and .net., and throws up a messagebox if the address doesn't fall within these parameters.

Private Sub txtEmailAddress_BeforeUpdate(Cancel As Integer)
 
 Dim DotEnding As String
 Dim InputErr As Integer
 
 DotEnding = Right(txtEmailAddress, 4)
 InputErr = 0
 
 If InStr(Me.txtEmailAddress, " ") > 0 Then
  InputErr = InputErr + 1
 End If
 
If Len(Me.txtEmailAddress) - Len(Replace(Me.txtEmailAddress, "@", "")) <> 1 Then
  InputErr = InputErr + 1
 End If
 
If DotEnding <> ".com" And DotEnding <> ".org" And DotEnding <> ".edu" And DotEnding <> ".net" Then
  InputErr = InputErr + 1
End If
 
If InputErr > 0 Then
 MsgBox "You Have Entered An Invalid Email Address!"
 Cancel = True
End If
 
End Sub

You'll have to decide what additional top-level domains, if any, that you want to accept, and modify the code according, following the example in Lines 17-19 above.

Here's an article on Wikipedia listing some of the more common ones:

http://en.wikipedia.org/wiki/Generic_top-level_domain

This code will also notify the user if an invalid address is copied then pasted into the field.

Linq ;0)>

[ADezii]

You can actually list an endless number of Domains while still keeping the code compact and somewhat efficient:

1. Create a Table named tblDomains
2. Create a Field within this Table named [Domain] {TEXT 4}
3. Enter all potential Domain Values within this Field: .com, .org, .edu. etc.
4. Incorporate the following logic into linq's code:
   

   If DCount("*", "tblDomains", "[Domain] = '" & DotEnding & "'") = 0 Then
     InputErr = InputErr + 1
   End If

[missinglinq]

Great enhancement, ADezii!

Linq ;0)>

[ADezii]

@missinglinq
Thank you linq, simply following in your footsteps!