Found country from Emailaddress 1

[petrovlis]

MsAccess 2007 query.
I have a table with emailaddresses from the world like

xxxx@yyyyy.tw
yyyyyyy@zzzzz.us
zzz@fff.cn

The most right two are representing the country, Taiwan,USA,China

I have a link to an other table with fields country and tw.us.cn

For updating and later import into say Outlook, I need to add the country field into above table AA

How to make the one to many link :
Right$([Fieldname],2) ??

Thanks for help

 

[Remou]

Perhaps something on the lines of:

SELECT Country.CountryAbbrev, t.Link 
FROM
  (SELECT Right([Email],2) AS Link
   FROM Table) t
INNER JOIN Country 
ON Country.CountryAbbrev =  t.Link

http://lessthandot.com

 

[petrovlis]

Sorry the (Right..,2) is not good enough. I need the most right AFTER the (point/dot) sign

I meant xxx@fff.us result to be : us
cccc@hhhhh.com result to be : com
vvv@jjjjj.gov result to be: gov

Thanks for prompt reply .
William

 

[Remou]

Try:

Mid(,InstrRev([Email],".")+1)

[URL unfurl="true"]http://lessthandot.com[/URL]

 

[petrovlis]

Thanks for good intelligent replies sofar,however I discovered that in my emailadresses there are also following:


xxxxx@fffff.edu.tw
ccccc@ggggg.un.us

but also the standard:

vvvvv@ggggg.cn

so I need to locate the most right "dot"and them make
an instr?? or right, of mid... to get the tw or us in
above cases


Thanks

 

[Remou]

Try the other way around then:

SELECT Table.Email, 
   (SELECT CountryAbbrev FROM Country 
    WHERE InStr(Table.Email,[CountryAbbrev ])>0) AS Co
FROM Table;

http://lessthandot.com

 

[PHV]

locate the most right "dot"
This is what the InStr[!]Rev[/!] function does.