Using Instr to compare 2 fields

[Vexorg]

Hello,

I need a query that will compare the 'Work Trading Name' to the 'Email1' field. I want to find all emails that do not have a similar domain.

<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-LEFT-COLOR: #d4d0c8; WIDTH: 543pt; BORDER-TOP-COLOR: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=723 colSpan=5 height=17>CORE

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>First Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Last Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Team Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Work Trading Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Email1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rey</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">ING INVESTMENT MANAGEMENT AMERICAS</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@inginvestment.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Andrés</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">MON GESTIO</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michelle</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BROADMARK ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@broadmarkasset.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michael</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">WADDELL & REED</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@waddell.com</TD></TR></TBODY></TABLE>


In the results I would like the following:

<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 212pt; mso-width-source: userset; mso-width-alt: 10313" width=282><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=74 height=20>Andrés</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 71pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=95>abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 61pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=81></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 212pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=282>MON GESTIO</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 143pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=191>abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR></TBODY></TABLE>



I tried the one below but was unable to get it to work properly.

select core.*
from core
WHERE instr(1,core.[Work Trading Name],core.[email1],1)=0

 

[jackd]

The key word in your post is similar. What do you mean by that, exactly?

select core.*
from core
WHERE 
InStr([email1],Mid(Replace([Work Trading Name]," ",""),1,5))=0

This will select records where

after removing spaces form the [Work Trading Name]
try to find the string of the first 5 characters of the [Work Trading Name]
anywhere in the [email1]

If you want to change the length of the string to match
change the 5 with your choice.

 

[revans]

I went ahead and created two functions: one to get the company name out of the email address and one to strip spaces.

Public Function GetCompanyEmailName(sEmail As String) As String
'return the company name from an email address
Dim temp As String
temp = Split(Split(sEmail, "@")(1), ".com")(0)
GetCompanyEmailName = temp
End Function

Public Function StripSpaces(str As String) As String
StripSpaces = Replace(str, " ", "")
End Function

These are called from the query like in jack's reply.

SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich

 

[Vexorg]

Hmm im having trouble making the function work Revans. Im trying to insert it has a module (the same way i would do a normal macro) but i cant seem to get it to work.

 

[Norie]

Couldn't you use the Like operator?

 

[revans]

By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich

 

[Norie]

Rich

Agree about the code but most text functions that are available in VBA are also available for expressions.

Not all of course.

 

[Vexorg]

By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich

Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.

 

[xenou]

Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.

That sounds strange. What exactly does the message say ... ? (I'm afraid I don't really believe it says "What is the name of a macro?" )

 

[revans]

That's what Run does; I've never found it all that useful. You'll want to call the function from your query like this

SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich