Why can't I compare a vba string type and a SQL short text type?

Question

I have a table with a short text field and a function that return a string type. When I compare the field to the returned value of the function a get the following error:

Data type mismatch on criteria expression.

Tables

Query

UPDATE myTable
SET clean = ""
WHERE trimWebAddress(webAddress) in (
SELECT domain FROM genericDomains)

Function

Public Function trimWebAddress(ByVal address As String) As String
    Dim cleanAddress As String

    If IsNull(address) = False Then
        cleanAddress = address
        If InStr(1, cleanAddress, "no_domain_available") = 1 Then
            cleanAddress = ""
        ElseIf InStr(1, cleanAddress, "https://") = 1 Then
            cleanAddress = Replace(cleanAddress, "https://", "")
        Else
            cleanAddress = Replace(cleanAddress, "http://", "")
        End If
        If InStr(1, cleanAddress, "www.") = 1 Then
            cleanAddress = Replace(cleanAddress, "www.", "")
        End If
        If InStr(1, cleanAddress, "/") > 0 Then
            cleanAddress = Left(cleanAddress, InStr(1, cleanAddress, "/") - 1)
        End If
    Else
        cleanAddress = ""
    End If
    trimWebAddress = cleanAddress
End Function

Gustav Gustav · Accepted Answer · 2016-08-28T10:40:18

You may have Null values in your data.

You can modify like this:

Public Function trimWebAddress(ByVal address As Variant) As String

    Dim cleanAddress As String

    If Nz(address) <> "" Then
        cleanAddress = address
        If InStr(1, cleanAddress, "no_domain_available") = 1 Then
            cleanAddress = ""
        ElseIf InStr(1, cleanAddress, "https://") = 1 Then
            cleanAddress = Replace(cleanAddress, "https://", "")
        Else
            cleanAddress = Replace(cleanAddress, "http://", "")
        End If
        If InStr(1, cleanAddress, "www.") = 1 Then
            cleanAddress = Replace(cleanAddress, "www.", "")
        End If
        If InStr(1, cleanAddress, "/") > 0 Then
            cleanAddress = Left(cleanAddress, InStr(1, cleanAddress, "/") - 1)
        End If
    End If

    ' Prevent zero-length output.
    If cleanAddress = "" Then
        cleanAddress = "NotToBeFound"
    End If

    trimWebAddress = cleanAddress

End Function

Or you may have to update to Null if zero-length string is not allowed:

UPDATE myTable
SET clean = Null
WHERE trimWebAddress(webAddress) in (
SELECT domain FROM genericDomains)

Why can't I compare a vba string type and a SQL short text type?

1 Answers