By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
11 Replies


P: n/a

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

P: n/a
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

P: n/a
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

P: n/a
Hi,
Mid(Email,instr(Email,"@"))
Sorry, so it's wrong, try :

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

Regards
Jens

Oct 22 '06 #5

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.