469,592 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

Query for getting domain suffix from E-mail address field?

Jan
I'd like a maketable query listing ClientID, and E-mail from another
table. This is simple, BUT I'd only like to get only the part after @
in the E-mail.

How would this work in MS Access 2003?

Oct 21 '06 #1
11 5151

Jan wrote:
I'd like a maketable query listing ClientID, and E-mail from another
table. This is simple, BUT I'd only like to get only the part after @
in the E-mail.

How would this work in MS Access 2003?
Something like...
Right$([email],len$([email])-InStr(1,[email],"@"))

Oct 22 '06 #2
Jan
Looks good, but for some strange reason Access says LEN is an undefined
function, I've pasted the sentence into the "field" field in the query.
LEN is a function when I check in Help...

pi********@hotmail.com wrote:
Jan wrote:
I'd like a maketable query listing ClientID, and E-mail from another
table. This is simple, BUT I'd only like to get only the part after @
in the E-mail.

How would this work in MS Access 2003?

Something like...
Right$([email],len$([email])-InStr(1,[email],"@"))
Oct 22 '06 #3
Hi,
>Right$([email],len$([email])-InStr(1,[email],"@"))
Looks good, but for some strange reason Access says LEN is an
undefined function, I've pasted the sentence into the "field" field
in the query. LEN is a function when I check in Help...
You could try

Mid(Email,instr(Email,"@"))

But I think it's better to check your references :

http://support.microsoft.com/kb/825796/en-us

Regards
Jens
Oct 22 '06 #4
Hi,
Mid(Email,instr(Email,"@"))
Sorry, so it's wrong, try :

Mid(Email,instr(Email,"@") +1 )

Regards
Jens

Oct 22 '06 #5
Jan
Thanks, MID works great :-)

My next challange is for domains to extract only the the last 2 levels,
like yahoo.com instead of server1.server.usa.yahoo.com

Jens Schilling wrote:
Hi,
Right$([email],len$([email])-InStr(1,[email],"@"))
Looks good, but for some strange reason Access says LEN is an
undefined function, I've pasted the sentence into the "field" field
in the query. LEN is a function when I check in Help...

You could try

Mid(Email,instr(Email,"@"))

But I think it's better to check your references :

http://support.microsoft.com/kb/825796/en-us

Regards
Jens
Oct 22 '06 #6
Hi,
My next challange is for domains to extract only the the last 2
levels, like yahoo.com instead of server1.server.usa.yahoo.com
That's all after the last but one dot, so try :

Function Get2Level(strDomain As String)
Dim i As Long, j As Long

i = InStr(1, StrReverse(strDomain), ".")
j = InStr(i + 1, StrReverse(strDomain), ".")

Get2Level = right(strDomain, j - 1)

End Function

Test in direct window :

?Get2Level("server1.server.usa.yahoo.com")
yahoo.com

Regards
Jens
Oct 22 '06 #7
Jan
How do I insert the function? I'm not used to programming in queries.
Trying to paste the code into the query only produces syntax error.

Where do I paste it?

Jens Schilling wrote:
Hi,
My next challange is for domains to extract only the the last 2
levels, like yahoo.com instead of server1.server.usa.yahoo.com

That's all after the last but one dot, so try :

Function Get2Level(strDomain As String)
Dim i As Long, j As Long

i = InStr(1, StrReverse(strDomain), ".")
j = InStr(i + 1, StrReverse(strDomain), ".")

Get2Level = right(strDomain, j - 1)

End Function

Test in direct window :

?Get2Level("server1.server.usa.yahoo.com")
yahoo.com

Regards
Jens
Oct 22 '06 #8
Hi,
How do I insert the function? I'm not used to programming in queries.
Trying to paste the code into the query only produces syntax error.
First you have to save the function in a (standard) modul.
Where do I paste it?
In your query you have to add a new column and insert something like this:

My2Level: Get2level([YourFieldName])

Where you have to replace < YourFieldName with the name of the field
containing the domain name.

But be aware - using such a function in a query won't be very fast.....

Regards
Jens


Oct 22 '06 #9
Jan
Getting run-time error 5, invalid procedure call or argument.

Seems like j=0 in the last line, i.e. j-1= -1 which might be why error
occurs. If doing i-1 instead i+1, it works - but then I only get the
last part of the domain instead of including the part before.

Jens Schilling wrote:
Hi,
How do I insert the function? I'm not used to programming in queries.
Trying to paste the code into the query only produces syntax error.

First you have to save the function in a (standard) modul.
Where do I paste it?

In your query you have to add a new column and insert something like this:

My2Level: Get2level([YourFieldName])

Where you have to replace < YourFieldName with the name of the field
containing the domain name.

But be aware - using such a function in a query won't be very fast.....

Regards
Jens
Oct 22 '06 #10
Hi,
Getting run-time error 5, invalid procedure call or argument.
Guess you have some records not matching your example (e.g. "yahoo.com") -
so there's no second dot in the string.
You can check the value of <j...

Function Get2Level(strDomain As String)
Dim i As Long, j As Long

i = InStr(1, StrReverse(strDomain), ".")
j = InStr(i + 1, StrReverse(strDomain), ".")

If j 0 Then
Get2Level = right(strDomain, j - 1)
Else
Get2Level = strDomain
End If

End Function

Regards
Jens


Oct 22 '06 #11
Jan
Thanks - yes, you're right I have some yahoo.com and some usa.yahoo.com
domains in the table. It works with the latest code.

Jens Schilling wrote:
Hi,
Getting run-time error 5, invalid procedure call or argument.

Guess you have some records not matching your example (e.g. "yahoo.com") -
so there's no second dot in the string.
You can check the value of <j...

Function Get2Level(strDomain As String)
Dim i As Long, j As Long

i = InStr(1, StrReverse(strDomain), ".")
j = InStr(i + 1, StrReverse(strDomain), ".")

If j 0 Then
Get2Level = right(strDomain, j - 1)
Else
Get2Level = strDomain
End If

End Function

Regards
Jens
Oct 22 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by manish | last post: by
13 posts views Thread by gsecrets | last post: by
1 post views Thread by owenmj | last post: by
7 posts views Thread by kooch54 | last post: by
3 posts views Thread by pbd22 | last post: by
2 posts views Thread by kelly.pearson | last post: by
4 posts views Thread by suganya | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.