Value isseue RRS feed

  • 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)
    End With
    'Remove the leading delimiter and return the result
    SimpleCSV = Mid$(strCSV & " ", Len(strDelim) + 1)
    Set rs = Nothing
    Set db = Nothing
    End Function

    Thursday, July 25, 2019 3:26 PM

All replies

  • 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].


    Hope it helps...

    Thursday, July 25, 2019 6:24 PM