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

Question

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

Kostas K. · Accepted Answer · 2020-10-30 16:19:56Z

1

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

answered Oct 30, 2020 at 16:19

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

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

Comments

Gustav · Accepted Answer · 2020-10-30 18:19:49Z

1

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

answered Oct 30, 2020 at 18:19

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related