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?

[pietlinden]

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],"@"))

[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],"@"))

[Jens Schilling]

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

[Jens Schilling]

Hi,

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

Sorry, so it's wrong, try :

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

Regards
Jens

[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

[Jens Schilling]

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

[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

[Jens Schilling]

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

[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

[Jens Schilling]

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

[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