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

 

[xenou]

Oh, got it. I thought this was after hitting Run Query. Makes sense now.

 

[Vexorg]

Oh, got it. I thought this was after hitting Run Query. Makes sense now.

It was after hitting the green triangle from the macro screen but see my post below



Ah I see now, you are supposed to run the module from the macro. Now i cant a Run time error 9 message and it highlights the line in red:

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

 

[revans]

Hrm. This works fine with your sample data. Error 9 is a subscript out of range error. This code assumes you are passing an email address of the format x@y.com. Do you have emails that aren't in that format? I'm now thinking .org would be a common thing that would break this code. I included the .com because I felt it's possible that an email address could have multiple dots but only one .com, but of course .org ruins that idea. I'd suggest trying jack's idea as it's essentially the same as mine, without the headache no doubt

hth,

Rich

 

[revans]

duplicate post...

 

[Vexorg]

Hrm. This works fine with your sample data. Error 9 is a subscript out of range error. This code assumes you are passing an email address of the format x@y.com. Do you have emails that aren't in that format? I'm now thinking .org would be a common thing that would break this code. I included the .com because I felt it's possible that an email address could have multiple dots but only one .com, but of course .org ruins that idea. I'd suggest trying jack's idea as it's essentially the same as mine, without the headache no doubt

hth,

Rich

Ah I see there are things like "co.uk" and "gob.mx" perhaps Ill just change it to check between the @ and . or just use jacks idea.

Thanks for the help guys

 

[Norie]

This function will check if any of the words in a search string are in a domain name.

Very lightly tested.

Function CheckDomain(strURL As String, strSearch As String) As Boolean
' checks if words in strSearch appear in domain of strURL
Dim strDOM As String
Dim arrWrds
Dim pos As Long
Dim I As Long
 
    pos = InStr(strURL, "@")
    
    If pos Then
        strDOM = Mid(strURL, pos + 1)
    Else
        strDOM = strURL
    End If
    
    pos = InStr(strDOM, ".")
    
    If pos Then
        strDOM = Left(strDOM, pos - 1)
    End If
    
    arrWrds = Split(strSearch, " ")
    
    For I = LBound(arrWrds) To UBound(arrWrds)
        pos = InStr(strDOM, arrWrds(I))
        
        CheckDomain = CheckDomain Or (pos <> 0)
    Next I
End Function
 
Sub test()
MsgBox CheckDomain("abc@inginvestment.com", "ING INVESTMENT MANAGEMENT AMERICAS")
End Sub

 

[Vexorg]

Hmm this gives me a runtime error 9 and when i go to debug it highlights this line

 temp = Split(Split(sEmail, "@")(1), ".com")(0)

 

[james_lankford]

the email address doesn't end in .com

it might be a .edu address

since it can't find a .com, the inner split doesn't find anything to split, so the outter split fails