Data type mismatch error in string query

Question

I'm trying to run the following query from Access's Create Query:

SELECT table.string, function(table.string) AS afterFix
INTO checkFix
FROM table
WHERE function(table.string)<>table.string;

I get the following error when trying to run the function: Data type mismatch in criteria expression.

table.string is defined as short text and it's field size is 50. some might be NULL, function is my vba function that returns a string. this is the function:

Public Function illegalCharEliminate(ByVal fieldName As String) As String
    Dim field As String
    field = fieldName
    If (IsNull(field)) Then
        GoTo catchNulls
    End If
    For i = 1 To 128        'goes through all illegal ascii and removes illegal chars
        Select Case i
            Case 1 To 44, 47, 58 To 59, 60 To 64, 91, 93, 123 To 125, 127
                 field = Replace(field, Chr(i), Empty)
           End Select
    Next i
catchNulls:
    illegalCharEliminate = field
End Function

I tried Cast and Convert sql functions:

WHERE function(table.string)<>(Cast(table.string as string))

but get this error" Syntax error (missing operator) in query expression function(table.string)... I saw some answers for this problem in vba, but I really do not want to use vba for this query. NOTICE: If there are no problems with my syntax, then the reason is probably because the table.string column has nulls. if so, how to fix this?

Don't know if it will solve it, but your query runs on table, so your criterium must be the other way around: WHERE table.string <> function(table.string) — Andre
Shouldn't your WHERE clause be function(table.string) and not function(string)? — Duston
@andre451 I changed it around, though I don't see how it makes any difference. Still get the same error, obviously. — MJH

Hambone Hambone · Accepted Answer · 2015-11-25T18:21:37

I think your assessment that the null is the problem was correct. The function fails when you give it a null value because a null is not a string (it's not anything). I think this fix might address your issue:

SELECT
  table.string,
  illegalCharEliminate(iif(isnull(table.string), "", table.string)) AS afterFix
FROM [table]
WHERE
  illegalCharEliminate(iif(isnull(table.string), "", table.string)) <> table.string;

In essence, this converts null values to an empty string.

Data type mismatch error in string query

1 Answers