Data type mismatch error in criteria expression

[xyba]

I have the following expression in a query field:

SELECT Documents.ID, IIf(InStrRev([MainDept] & ("/"+[Referral1]) & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]),"/")=0,[MainDept],Mid([MainDept] & ("/"+[Referral1]) & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]),InStrRev([MainDept] & ("/"+[Referral1]) & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]),"/")+1)) AS Expr1, Documents.SubjectID, Documents.FirstName, Documents.LastName, Documents.Received, Documents.DocID
FROM Documents;

When I add a text criteria to it and change back to datasheet view I get the Data Type mismatch error. Each of the fields referenced in the expression are text fields and the criteria I set is set in quotes so not sure why I'm getting this error.

Any ideas please?

 

[xyba]

OK, I've actually figured out the issue, there were #NA errors in the table. But this brings me to another question...how can I get the above query expression to ignore records that have #NA in that field?

 

[arnelgp]

a better way to do this is create a function in a module that will do what you want:

Public Function fnLastDept(ParamArray dept() As Variant) As Variant
    Const delim As String = " "
    Dim s As String
    Dim l As Long
    Dim i As Integer
    For i = LBound(dept) To UBound(dept)
        s = (IIf(Trim(dept(i) & "") = "", Null, dept(i)) + delim) & s
    Next i
    l = InStr(s, delim)
    If (l <> 0) Then
        fnLastDept = Left(s, l - 1)
    Else
        fnLastDept = s
    End If
End Function

to include in your query:

SELECT Documents.ID, fnLastDept([MainDept], [Referral1], [Referral2], [Referral3], [Referral4], [Referral5]) AS Expr1, Documents.SubjectID, Documents.FirstName, Documents.LastName, Documents.Received, Documents.DocID
FROM Documents;

in a control on a form:

= fnLastDept([MainDept], [Referral1], [Referral2], [Referral3], [Referral4], [Referral5])