Access 2010 - Compare email and URL extensions

select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],4)

Actually I should have said a query using "Like" to get them to match up

select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],5)

you can also use this

select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],instr([url],"www.")+4)
and [email] & ""<>"" and [url] & ""<>""

I am getting a syntax error - missing operator

select email.email,RAIDatabasesFirms.Websites
where mid([email],instr([email],"@")+1)=mid([Websites],4)

You said an "update query", what are you trying to update?

In addition to using the WHERE clause, as capricorn1 has done, you could also use a non-equi join.  But this type of join is not visible in the query design window.

SELECT T1.Email, T2.URL
FROM T1
INNER JOIN T2
ON mid(T1.Email, instr(T1.email, "@") + 1) = mid(T2.URL, 5)

Actually I should have said a query using "Like" to get them to match up - I do need the grid view if possible to do additional things

Great thanks.

test this

select email.email,RAIDatabasesFirms.Websites, mid([email],instr([email],"@")+1)
FROM email, RAIDatabasesFirms
where mid([email],instr([email],"@")+1) Like mid([Websites],5)

or use:

where [Websites] Like "*" & mid([email],instr([email],"@")+1)

No matter what I do this operation locks up Access 2010.  I tried it in Access 2003 with the same result.  The amount of records the query is bringing back is about 7,500 so i don't see what the problem is.  I have programs running ten times that many records with no problems.

might be better to create a new field for both tables, "URLName"
then run an update queries

Update Email set [URLName]=mid([email],instr([email],"@")+1) Where [email] & ""<>""

Update RAIDatabasesFirms set [URLName]=mid([Websites],5) Where [Websites] & ""<>""


then create a query

select Email.*
From Email E inner join RAIDatabasesFirms R
On E.UrlName=R.UrlName

which operation?

How many records in each of those two tables?  The way the query is written, using a WHERE clause instead of a JOIN will cause a Cartesian join, with the number of records in the basic recordset = (# in table A) * (# in table B).
The WHERE clause will filter those that don't meet the criteria, but it will initially develop a recordset as indicated above.

Is this against a SQL Server database, I remember seeing a similar question after you accepted the answer to this question that looked like it was pointing to SQL Server and was having difficulty with the instr() function.

What SQL are you using now?

Actually i walked away to help someone and when I got back it finally finished the query without locking up.  I am guessing that no key field between these tables causes it to take a long time.  Thanks for the responses!

I am getting for example johndoe@cs.com is matching up with www.companynamecs.com, it is grabing any pattern that matches.  Any way to start the matchup after the "www"?

Again, which query are you using?  You might try:

where [Websites] Like "*." & mid([email],instr([email],"@")+1)

Sorry about that - here is the query

SELECT email.email, RIADatabaseFirm.Websites, Mid([email],InStr([email],"@")+1) AS Expr1 INTO MaxExport
FROM email, RIADatabaseFirm
WHERE (((RIADatabaseFirm.Websites) Like "*" & Mid([email],InStr([email],"@")+1)));

Select allOpen in new window

Try adding the period (.) after the asterisk, as I indicated in my previous post.

Since you are just dumping these fields into a temporary table, try:

SELECT email.email, RIADatabaseFirm.Websites, Mid(email.[email],InStr([email],"@")+1) AS Expr1
INTO MaxExport
FROM email
INNER JOIN RIADatabaseFirm
ON RIADatabaseFirm.Websites Like "*." & Mid(email.[email],InStr(email.[email],"@")+1)));

From what I saw from the results it looks like the period made the difference.  Many thanks

still might want to try the non-equi join.

It is a good tool to have in your tool bag, if you understand how it works.