CODEWITHSUNDEEP
sqlvbams-access
MJH
MJH

Reputation: 859

Data type mismatch error in string query

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?

Upvotes: 1

Views: 3315

Answers (1)

Hambone
Hambone

Reputation: 16377

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.

Upvotes: 1

Related Questions