CODEWITHSUNDEEP
vbams-access
Andrew
Andrew

Reputation: 105

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

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:

Upvotes: 0

Views: 118

Answers (2)

Gustav
Gustav

Reputation: 55981

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

Upvotes: 1

Kostas K.
Kostas K.

Reputation: 8518

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

Upvotes: 1

Related Questions