Access - "Data type mismatch in criteria expression" with VBA function

0
votes

I get error Data type mismatch in criteria expression when running this query. See below what I tried. Question is how can I investigate further to find the error?

SELECT qCalls.Senso, qCalls.Data, qCalls.Ora, qCalls.NumeroPulito, qCalls.Durata, qContactsOutlookPerCallsUNION.Azienda, IIf(Count([NOME])=1,First([NOME]),"**nomi multipli**") AS Nome2
FROM qCalls INNER JOIN qContactsOutlookPerCallsUNION ON qCalls.NumeroPulito = qContactsOutlookPerCallsUNION.Numero
GROUP BY qCalls.Senso, qCalls.Data, qCalls.Ora, qCalls.NumeroPulito, qCalls.Durata, qContactsOutlookPerCallsUNION.Azienda
ORDER BY qCalls.Data DESC;

Both qCalls and qContactsOutlookPerCallsUNION run correctly when called separately. There is no criteria expression (= WHERE clause, as I understand it) in my SQL. I then think the data type issue is on the INNER JOIN part but:

  • qCalls.NumeroPulito is a string, comes from: CStr(Replace([Number],"+39","")) AS NumeroPulito
  • qContactsOutlookPerCallsUNION.Numero is a string, it comes from: IIf(IsNull([Phone]),Null,PulisciTelPerCalls([Phone])) AS Fisso where PulisciTelPerCalls() is a VBA function which returns a string
2
vbams-access

2 Answers

1
votes

Without being too sure, I believe the error is caused by the inline if statement IIF() since it checks both conditions anyway, thus could be sending a null value to the function.

I think you should scrap the IIF and handle null values in the function.

Public Function PulisciTelPerCalls(ByVal Phone As Variant) As String
    If IsNull(Phone) Then
        PulisciTelPerCalls = vbNullString
        Exit Function
    End If

    'rest of method 
End Function

Then just call the method directly:

PulisciTelPerCalls([Phone]) AS Fisso
1
votes

CStr on a string doesn't make sense. Try removing it and use Nz:

Replace([Number],"+39","") AS NumeroPulito

and

PulisciTelPerCalls(Nz([Phone])) AS Fisso

or

IIf(IsNull([Phone]),"",PulisciTelPerCalls([Phone])) AS Fisso