Results 1 to 3 of 3
  1. #1
    Aaron is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    1

    Exclamation Trying to return multiple results in one line from a query

    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

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    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.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 09-02-2010, 01:59 PM
  2. Replies: 1
    Last Post: 02-02-2010, 07:19 AM
  3. Multiple Query Results Sorted Together
    By Rawb in forum Reports
    Replies: 1
    Last Post: 12-10-2009, 04:05 PM
  4. Replies: 5
    Last Post: 10-08-2009, 05:15 AM
  5. Line Return escape character
    By Jerry8989 in forum Access
    Replies: 8
    Last Post: 10-02-2009, 09:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums