Type mismatch error

[broodmdh]

I have a some vba that runs a query (which someone else wrote) and then spits out the results in an html file. Everything works as expected except for one particular field, which throws a 'Error 13 - Type Mismatch' error whenever I try and reference it. The field in the table is defined as Text, and when I view the Fields values in my recordset that particular field is of type 109 (dbComplexText). If I try to run the query itself in SQL View, it works perfectly (but the result for that field seems to be a combobox with the values already selected). I've tried testing for Null, but that isn't the problem.

The query:

Dim rs As DAO.Recordset, fs, TextFile, strSQL As String
Set fs = CreateObject("Scripting.FileSystemObject")
myPath = Application.CurrentProject.Path & "\"
myFileName = "pa.html"
Set TextFile = fs.CreateTextFile(myPath & myFileName)
    
strSQL = "SELECT [PA Recommendations].[Rec No], [PA Recommendations].[Recommendation Text], [NAFO Actions].[NAFO Doc No], [NAFO Actions].[Body Responsible], [NAFO Actions].[Comment], [NAFO Actions].[Timescale], [Action Status].[Progress Status] FROM ([Action Status] INNER JOIN [PA Recommendations] ON [Action Status].[Progress Status] =[PA Recommendations].[Progress Status]) INNER JOIN [NAFO Actions] ON [PA Recommendations].[Rec No] =[NAFO Actions].[Rec No] ORDER BY [PA Recommendations].[Rec No]"
Set rs = CurrentDb.OpenRecordset(strSQL)

....

' This line throws the error
TextFile.writeline ("<td>" & rs.Fields("Body Responsible") & "</td>")

Any thoughts? VBA and Access are not things I get to work with very often, so I'm hoping the answer may be obvious to others.

[broodmdh]

It seems to me like rs.Fields("Body Responsible") is referring to a multivalued field, which makes sense. I'm not able to get the comma-separated values by simply referring to the field itself, though. I'm not sure how else to do it, though. Referring to rs.Fields("Body Responsible").Value works, but only returns one of the selected values.

[ItsMe]

It seems to me like rs.Fields("Body Responsible") is referring to a multivalued field, which makes sense. I'm not able to get the comma-separated values by simply referring to the field itself, though. I'm not sure how else to do it, though. Referring to rs.Fields("Body Responsible").Value works, but only returns one of the selected values.

Assuming your script can manage the CSV to HTML..... I would look at the data table within Access. You mentioned you are dealing with text data types and the error is 'Mismatch'. Did you check the text properties within the table and make sure it is 255 characters or less and a text data type that can translate to HTML?

If your text is pushing out of Access as dbComplexText, this may be what your script is struggling with.

Just throwing out an observation I made. I have not researched this. In fact, the reason I joined this forum is I am beginning to upgrade my Access DB's to 2010 and need to learn about it myself.

[broodmdh]

It turns out that it was actually defined as a Field2 object, and the following code allowed me to get what I needed.

 Dim fld As DAO.Field2
 Dim rs2 As DAO.Recordset
 bodies = ""
 Set fld = rs.Fields("Body Responsible")
 If fld.IsComplex Then
    Set rs2 = fld.Value
 End If
 Do While Not rs2.EOF
    bodies = bodies & rs2("Value") & ", "
     rs2.MoveNext
 Loop
rs2.Close