I was referring to my query where I included both tables - I did not attach anything here - provided the sql
If some of the names start with spaces, run an update query first to clean up that mess.
What is missing? We can't see your results. We have no idea what your data looks like so we have no idea what might be wrong with your query.
I did not attach anything here - provided the sql
Without sample data is worse than guessing...Your data could be everything..
One thing is certain...the Instr is probably a performance killer...
Hi Coleen
As the others had said, it's a bit difficult to say if there is a better way without seeing an example of the data from both tables. Just a few rows from each would be helpful.
With that said:
a. You never want a query without a join if you can avoid it. Even a join using a WHERE expression is better than nothing.
b. HAVING is always applied after the records are fetched. Always try and put any restrictions on the data as a WHERE.
Jim.
My query provides results, but they are not complete.How do you know that?
Option Explicit
Public Function PartialCodeMatch(ByVal parmBusinessCode As Variant) As Boolean
Dim vParts As Variant
Dim vItem As Variant
If IsNull(parmBusinessCode) Then
PartialCodeMatch = False
Exit Function
End If
vParts = Split(parmBusinessCode, " ")
For Each vItem In vParts
If IsNull(DLookup("Code", "[T_BusinessCodes]", "code='" & Replace(vItem, "'", "''") & "'")) Then
Else
PartialCodeMatch = True
Exit Function
End If
Next
PartialCodeMatch = False
End Function
SELECT T_UCVI.ID, T_UCVI.BUSINESS_NAME
FROM T_UCVI
WHERE PartialCodeMatch([T_UCVI].[BUSINESS_NAME])=True
ORDER BY T_UCVI.ID;
Public Function RegexMatch(ByVal parmBusinessCode As Variant) As Boolean
Static oRE As Object
Dim rs As Recordset
Dim strCodes As String
If oRE Is Nothing Then
Set rs = DBEngine(0)(0).OpenRecordset("Select Code from [T_BusinessCodes]")
Do Until rs.EOF
strCodes = strCodes & "|" & Trim(rs!Code)
rs.MoveNext
Loop
Set oRE = CreateObject("vbscript.regexp")
oRE.Global = False
oRE.ignorecase = True
oRE.pattern = "\b(?:" & Mid(strCodes, 2) & ")\b"
End If
If IsNull(parmBusinessCode) Then
RegexMatch = False
Exit Function
End If
RegexMatch = oRE.test(parmBusinessCode)
End Function
SELECT T_UCVI.ID, T_UCVI.BUSINESS_NAME
FROM T_UCVI
WHERE RegexMatch([T_UCVI].[BUSINESS_NAME])=True
ORDER BY T_UCVI.ID;
Since I can't run your queryYou are receiving a Undefined Function RegexMatch in expression?
BUSINESS_NAME
**************N├O USAR******************
*NON Usare
I'm not clear how this could be misunderstood.For me, it isn't a matter of "understanding", rather it is a matter of "why would someone do such a thing in the first place". I'm sure that you or your client has a reason.
Public Function RegexMatch(ByVal parmBusinessCode As Variant) As Boolean
Static oRE As Object
Dim rs As Recordset
Dim strCodes As String
If oRE Is Nothing Then
Set rs = DBEngine(0)(0).OpenRecordset("Select Code from [T_BusinessCodes]")
Do Until rs.EOF
strCodes = strCodes & "|" & Replace(rs!Code, ".", "\.")
rs.MoveNext
Loop
Set oRE = CreateObject("vbscript.regexp")
oRE.Global = False
oRE.ignorecase = True
oRE.pattern = Mid(strCodes, 2)
End If
If IsNull(parmBusinessCode) Then
RegexMatch = False
Exit Function
End If
RegexMatch = oRE.test(parmBusinessCode)
End Function
Replace(rs!Code, ".", "\.")
In the above piece of code, I'm replacing every period character with two characters ("\."), a backslash followed by a period character.I thought you were trying to capture record 4128 which was missing the last period. But if it's not there, how can you test for it, right?In regex patterns, the backslash character is an escape for the character that follows it. This is necessary in cases where the character we want to match is one of those we use for regex pattern definitions.
I don't see any attachments.