Trying to return multiple results in one line from a query

[Aaron]

hey
i'm currently trying to get a report to show other items with the same job number in a field. this code worked in access 03 but when i upgraded and converted the database it now doesnt work. i keep getting runtime error 13, type mismatch. the main problem is that i didnt write the original code but im stuck trying to fix it.

here is my code:

Public Function find_others(JobID As Long) As String

Dim db As Database, rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [CalibrationNumber] FROM [Calibrations] WHERE CalibrationJobID = " & JobID)



find_others = ""

With rs
While Not .EOF
find_others = find_others & "C" & ![CalibrationNumber] & ", "
.MoveNext
Wend
End With

End Function

Any help would be hugely appreciated.
thanks

[NTC]

you know I looked at this - and the only thing that crosses my mind off hand is.......... I wonder if Find_Others needs to be explicitly Dim as string

and also why is that ! in front of the [CalibrationNumber] ?

kinda don't think either point helps - but you might try.... Dim the string and remove that ! ... and see what happens.

[orange]

I mocked up a sample using your function and my data and it works fine in acc2003.

I did a search for error 13 type mismatch and found a response that conversions are more stringent in acc2007 and acc2010 than earlier versions. It could be the Long associated with JobID???
How is [CalibrationNumber] defined (Dimmed)? Could be a conversion issue?

The response I found was at http://social.msdn.microsoft.com/For...3-60b9ee9882ab

and dealt specifically with a Date being dimmed as string-- OK in 2000 but gives error 13 in 2007.