How to use VBA function in where condition?

Question

I'm using the Levenshtein distance from here in my Access Database. Using the functionin a SELECT-Statement works when the function is in the field list. e.g.:

SELECT field, Levenshtein(field, 'Saturday')
FROM table

Where field is a Text-Column (Access-VarChar). Now, I want to use the function in the where-clause as a condition like

SELECT field, Levenshtein(field, 'Saturday') as distance
FROM table
WHERE (Levenshtein(field, 'Saturday') < 5)

But all Access gives me is an error saying "Conflict with types". Its the same when using distance in the consition instead of Levenshtein(field, 'Saturday').

The levenshtein-function is defined as Public Function Levenshtein(string1 As String, string2 As String) As Long. So what did I do wrong?

What data type is `field`? Is it a DataTime data type? – HK1 Nov 16 '12 at 14:24 — HK1, Nov 16 '12 at 14:24
I forgot to say: `field` is Text (Access-VarChar). – rollstuhlfahrer Nov 17 '12 at 21:55 — rollstuhlfahrer, Nov 17 '12 at 21:55

fthiella · Accepted Answer · 2012-11-16T15:13:21.207

2

I think you have some null values in your table. Could you try this query instead?

SELECT field, Levenshtein(field, 'Saturday') as distance
FROM table
WHERE (Levenshtein(nz(field, ""), 'Saturday') < 5)

(it looks like that a where field is not null condition in your query is not enough, the function is tried to be evaluated anyway)

Or you can define your Levenshtein function as (string1 as Variant, string2 as Variant), and make sure you return null if string1 or string2 is null:

If IsNull(string1) or IsNull(string2) Then
  Levenshtein = Null
  Exit Function
End If

edited Nov 16 '12 at 15:13

answered Nov 16 '12 at 15:02

fthiella

48,073
15
90
106

Have to wait until monday to test this. – rollstuhlfahrer Nov 17 '12 at 22:14
Thanks for the hint. I would have never checked for `null` values. – rollstuhlfahrer Nov 19 '12 at 07:11

How to use VBA function in where condition?

1 Answers1