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
– Andre, Commented Nov 25, 2015 at 15:20
Shouldn't your WHERE clause be function(table.string) and not function(string)? — Duston
– Duston, Commented Nov 25, 2015 at 15:25
@andre451 I changed it around, though I don't see how it makes any difference. Still get the same error, obviously. — MJH
– MJH, Commented Nov 25, 2015 at 15:50

Hambone · Accepted Answer · 2015-11-25 18:21:37Z

1

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.

answered Nov 25, 2015 at 18:21

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MJH Over a year ago

thank you so much. This worked perfectly. I didn't know how to do conditional statements in access beforehand, thanks!

Collectives™ on Stack Overflow

Data type mismatch error in string query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related