Dragonzstealth
New Member
- Joined
- Jan 27, 2006
- Messages
- 13
Hi guys, just wondering if anyone out there in internet land knows an input mask for e-mail addresses. I'm apparently too dumb to figure this one out on my own. Thanks.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
E-mail Input Mask
- Thread starter Dragonzstealth
- Start date
VicRauch
Well-known Member
- Joined
- Mar 23, 2006
- Messages
- 2,032
I don't think you will find an input mask, but what I would think would help would be something to check the email address during the BeforeUpdate event.
I would think that this routine would check for blanks (should be none), make sure there is only one "at" sign (@), and then something that looks like a valid domain address which would be xxxxx.xxxx or xxxxx.xxxxx.xx.xxxx. In other words, at least one period (.).
Someone might even have this already coded and could share it with you.
I would think that this routine would check for blanks (should be none), make sure there is only one "at" sign (@), and then something that looks like a valid domain address which would be xxxxx.xxxx or xxxxx.xxxxx.xx.xxxx. In other words, at least one period (.).
Someone might even have this already coded and could share it with you.
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi
As you may be aware, input masks are used to control the way data is captured but they don't naturally lend themselves to text fields of variable length. If you are using a form to enter the e-mail addresses, you can use a 'before update' event to ensure the address conforms to your expectations. Or you could use a validation rule (at the table level) like this:
Like "*" & "@" & "*" & "." & "*"
With this validation rule the user has to input the ampersand and decimal point.
HTH, Andrew
As you may be aware, input masks are used to control the way data is captured but they don't naturally lend themselves to text fields of variable length. If you are using a form to enter the e-mail addresses, you can use a 'before update' event to ensure the address conforms to your expectations. Or you could use a validation rule (at the table level) like this:
Like "*" & "@" & "*" & "." & "*"
With this validation rule the user has to input the ampersand and decimal point.
HTH, Andrew
Upvote
0
Dragonzstealth
New Member
- Joined
- Jan 27, 2006
- Messages
- 13
Thanks a lot guys, the info really helped!
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi
Apprently not. Well spotted. Keep in mind both have errors in that you can enter just @. and it will accept it (or something like 1@.). I think my format was a carry-over from a failed attempt at an input mask. The more correct way would be to use a VBA solution.
Andrew
Apprently not. Well spotted. Keep in mind both have errors in that you can enter just @. and it will accept it (or something like 1@.). I think my format was a carry-over from a failed attempt at an input mask. The more correct way would be to use a VBA solution.
Andrew
Upvote
0
Oorang
Well-known Member
- Joined
- Mar 4, 2005
- Messages
- 2,071
I truthfully wasn't sure. Good point on the holes though. I am building a new d-base anyhow, (hence me searching out this thread) 
so I had to invest some time in this task one way or the other. So here is what I have whipped up so far (greatly modified thanks to your point about the problems with *@*.*).
For the "@." problem you can force any number of characters you want in between literals with "?". So I use the ?*@?*.?* mask to get a good general overall pattern:
Force a minimum length of 5 (your 2 literals and 3 fillers) and all lowercase using input mask:
(My field is 50 characters.)
I also put in lenth restrictions on the validation rule should the input mask not make it to the form for some reason.
Use the COUNTIN formula provided by pgc01 (http://www.mrexcel.com/board2/viewtopic.php?t=234046) to unsure only 1 occurance of "@".
I am still working on a ways to eliminate forbidden characters.
But for now the combined validation is this:
so I had to invest some time in this task one way or the other. So here is what I have whipped up so far (greatly modified thanks to your point about the problems with *@*.*). For the "@." problem you can force any number of characters you want in between literals with "?". So I use the ?*@?*.?* mask to get a good general overall pattern:
Code:
[MyField]) like "?*@?*.?*"(My field is 50 characters.)
Code:
<&&&&&CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
Code:
LenB([MyField])>8 and lenB([MyField]) < 102
Code:
(LenB([MyField]) - LenB(Replace([MyField], "@", ""))) / LenB("@")=1But for now the combined validation is this:
Code:
[MyField] like "?*@?*.?*" and LenB([MyField])>8 and lenB([MyField]) < 102 and (LenB([MyField]) - LenB(Replace([MyField], "@", ""))) / LenB("@")=1
Upvote
0
giacomo
Well-known Member
- Joined
- Feb 20, 2002
- Messages
- 1,796
you could also use the split function to get a count of @'s:
hth,
Giacomo
Code:
Sub SplitTest()
Debug.Print UBound(Split("giacomo.spam@gmail.com", "@"))
Debug.Print UBound(Split("giacomo@spam@gmail.com", "@"))
Debug.Print UBound(Split("giacomo.spam@@gmail.com", "@"))
Debug.Print UBound(Split("giacomo.spam.gmail.com", "@"))
End Subhth,
Giacomo
Upvote
0
giacomo
Well-known Member
- Joined
- Feb 20, 2002
- Messages
- 1,796
OK, you win on the ubound/split but you can totally use UDF's. I used j-walk's CountOccurrences function as a test and it worked fine.
Rich (BB code):
Function CountOccurrences(str, substring) As Long
' Returns the number of times substring appears in str
Dim x As Variant
x = Split(str, substring)
CountOccurrences = UBound(x)
End Function
Rich (BB code):
SELECT CountOccurrences([email_addr],"@") AS AtCount
FROM someTable;hth,
Giacomo
Upvote
0
