Data Type Mismatch in criteria Expression (modded fuzzy string match)

[christianlott1]

Taken (and modded) from http://www.codeguru.com/vb/gen/vb_misc/tips/
article.php/c13137

'RESULTS' table:

REF_STRING - TEXT 250
TEST_STRING - TEXT 250
MATCH_VALU - SINGLE FIXED 2 DECIMAL PLACES

(I also have three additional fields but they are not used in this
query - id, fid, good_match)

>From the query:

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, LCS([REF_STRING],
[TEST_STRING]) AS Expr1
FROM REF_LIST, TEST_LIST
WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

---

Public Function LCS(String1 As String, String2 As String, Optional
AlgInUse) As Single
'* *************** Longest Common Subsequence *********************
'* The LCS is calculated by the length of the longest common,
'* not necessarily contiguous, sub-sequence of characters divided by
'* the average character lengths of both strings.
'* In this case: c(m, n) / (((Str1Len) + (Str2Len)) / 2))
'* LCS is symmetric.
'*
'* str1 and str2 are arrays.
'************************************************* ***********************

Dim STR1() As Byte
Dim STR2() As Byte

STR1 = StrConv(String1, vbFromUnicode)
STR2 = StrConv(String2, vbFromUnicode)

Dim i As Integer, j As Integer, m As Integer, n As Integer
Dim c() As Integer, b() As Integer, X$(), Y$()
Dim Str1Len As Integer, Str2Len As Integer, SmStr(), LgStr()

If IsMissing(AlgInUse) Then AlgInUse = ""

Str1Len = UBound(STR1)
Str2Len = UBound(STR2)

n = Minimum(Str1Len, Str2Len)
m = Maximum(Str1Len, Str2Len)

ReDim X(m)
ReDim Y(m)
ReDim c(m, m)
ReDim b(m, m)

If Str1Len Str2Len Then
For i = 0 To Str1Len - 1
X(i) = STR1(i)
Next i
For i = 0 To Str2Len - 1
Y(i) = STR2(i)
Next i
Else
For i = 0 To Str2Len - 1
X(i) = STR2(i)
Next i
For i = 0 To Str1Len - 1
Y(i) = STR1(i)
Next i
End If ' Str1Len Str2Len

For i = 1 To m
For j = 1 To n
If X(i - 1) = Y(j - 1) Then
c(i, j) = c(i - 1, j - 1) + 1
b(i, j) = 1 ' /* from north west */
ElseIf c(i - 1, j) >= c(i, j - 1) Then
c(i, j) = c(i - 1, j)
b(i, j) = 2 ' /* from north */
Else
c(i, j) = c(i, j - 1)
b(i, j) = 3 ' /* from west */
End If
Next j
Next i

' return c[m][n];

If c(m, n) 0 Then
If AlgInUse = "Dmph" Or AlgInUse = "SSLCS" Then
LCS = c(m, n) ' Longest Common Subsequence
Else
LCS = CSng(Format((c(m, n) / (((Str1Len) + (Str2Len)) / 2)),
"#.##"))
End If
Else
LCS = 0
End If

Erase X
Erase Y
Erase c
Erase b

End Function

[storrboy]

>INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )

>SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, LCS([REF_STRING],
[TEST_STRING]) AS Expr1
FROM REF_LIST, TEST_LIST
WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

I'm not familiar with the function you are trying to use, but aren't
you calling the LCS function twice in this query?
You call it once in the field list and alias the field as Expr1, then
again in the WHERE clause.
I would think that your WHERE statement could simply be
WHERE([Expr1]>0.79);

[christianlott1]

On Feb 12, 9:27 pm, "storrboy" <[e-mail]:

I would think that your WHERE statement could simply be
WHERE([Expr1]>0.79);

Yeah, I tried that before and just again when you suggested it. It
creates a pop up box that asks me for Expr1 as a parameter value.

I might need to contact the author. I thought it might be obvious to
someone else though.

Thanks,

Christian

[Bruce]

On Feb 13, 11:24 am, [e-mail] wrote:

On Feb 12, 9:27 pm, "storrboy" <[e-mail]:

I would think that your WHERE statement could simply be
WHERE([Expr1]>0.79);

Yeah, I tried that before and just again when you suggested it. It
creates a pop up box that asks me for Expr1 as a parameter value.

I might need to contact the author. I thought it might be obvious to
someone else though.

Thanks,

Christian

No, you actually need both (you can't reference Expr1 in your where
clause).

I'm not sure what the actual question is here (maybe my newsreader
truncated it), but perhaps you are missing a join between REF_LIST and
TEST_LIST, e.g.

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, LCS([REF_STRING],
[TEST_STRING]) AS Expr1
FROM REF_LIST
INNER JOIN TEST_LIST
ON REFLIST.ID = TEST_LIST.ID
WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

Also if REF_STRING or TEST_STRING exist in both tables you'll need to
make that clear where you reference the fields in your SELECT, e.g.

LCS([REF_STRING],[TEST_STRING]) maybe should be
LCS(REF_LIST.REF_STRING, TEST_LIST.TEST_STRING)

(you don't need the brackets)

Bruce

[christianlott1]

On Feb 13, 12:30 pm, "Bruce" <[e-mail]:

No, you actually need both (you can't reference Expr1 in your where
clause).

I'm not sure what the actual question is here (maybe my newsreader
truncated it), but perhaps you are missing a join between REF_LIST and
TEST_LIST, e.g.

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, LCS([REF_STRING],
[TEST_STRING]) AS Expr1
FROM REF_LIST
INNER JOIN TEST_LIST
ON REFLIST.ID = TEST_LIST.ID
WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

Also if REF_STRING or TEST_STRING exist in both tables you'll need to
make that clear where you reference the fields in your SELECT, e.g.

LCS([REF_STRING],[TEST_STRING]) maybe should be
LCS(REF_LIST.REF_STRING, TEST_LIST.TEST_STRING)

(you don't need the brackets)

Thanks for taking the time to look at this.

There is no join between the tables. Also, in Ref_List there is only
one field, Ref_String, in Test_List there is only one field,
Test_String.

The problem is it starts the query for a bit then pops up with the
error message " Data Type Mismatch in Criteria Expression "

I assume it's because of the Single datatype, but the table has the
correct data type (RESULTS - MATCH_VALU) and the function formats the
result as a single.

The error message is obviously referring to

WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

but I can't tell what's wrong with it.
Christian

[Bruce]

On Feb 13, 2:23 pm, [e-mail] wrote:

On Feb 13, 12:30 pm, "Bruce" <[e-mail]:

No, you actually need both (you can't reference Expr1 in your where
clause).

I'm not sure what the actual question is here (maybe my newsreader
truncated it), but perhaps you are missing a join between REF_LIST and
TEST_LIST, e.g.

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, LCS([REF_STRING],
[TEST_STRING]) AS Expr1
FROM REF_LIST
INNER JOIN TEST_LIST
ON REFLIST.ID = TEST_LIST.ID
WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

Also if REF_STRING or TEST_STRING exist in both tables you'll need to
make that clear where you reference the fields in your SELECT, e.g.

LCS([REF_STRING],[TEST_STRING]) maybe should be
LCS(REF_LIST.REF_STRING, TEST_LIST.TEST_STRING)

(you don't need the brackets)

Thanks for taking the time to look at this.

There is no join between the tables. Also, in Ref_List there is only
one field, Ref_String, in Test_List there is only one field,
Test_String.

The problem is it starts the query for a bit then pops up with the
error message " Data Type Mismatch in Criteria Expression "

I assume it's because of the Single datatype, but the table has the
correct data type (RESULTS - MATCH_VALU) and the function formats the
result as a single.

The error message is obviously referring to

WHERE (((LCS([REF_STRING],[TEST_STRING]))>0.79));

but I can't tell what's wrong with it.

Christian

Can REF_STRING or TEST_STRING ever be null? If so, I bet that's what
the problem is. Your LCS function will crash if either of these are
null.

Bruce

[christianlott1]

On Feb 14, 12:14 pm, "Bruce" <[e-mail]:

Can REF_STRING or TEST_STRING ever be null? If so, I bet that's what
the problem is. Your LCS function will crash if either of these are
null.

ARG! That was it mate!

THANKS!!