Value isseue

Question

I have attempted to use the following in a query to call the information I need, but I receive an Error:  3463 Data  type mismatch in criteria expression.

Phone: SimpleCSV("SELECT [Information] FROM [Contact Info] WHERE [ContactID]='" & [Attendant] & "'")

I believe that it is the [Attendant] because in the current querry it is a lookup field where the string related to the ID is displayed.  How do I get this field to be the ID value in the criteria portion of the function?  Or can you provide me with any other direction to go?

The public function I am using follows below:

Public Function SimpleCSV(strSQL As String, Optional strDelim As String = ", ") As String

'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV & " ", Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function



Answer 1

Hi. If [Attendant] is a MVF, then it's probably returning multiple values. If so, you could try it this way:

Phone: SimpleCSV("SELECT [Information] FROM [Contact Info] WHERE [ContactID] In(" & SimpleCSV(SELECT [Attendant].Value FROM Information WHERE ID=" & [ID]) & ")")

Please make sure to use the actual names of your fields and table especially the primary key field where I simply used [ID].

(untested)

Hope it helps...