'Cannot join on Memo, OLE, or Hyperlink Object' Error

  • gomikem

    SSC-Addicted

    Points: 488

    I have some SQL in the code of one of my reports that calls fields from linked (SQL) tables and some pass through queries stored in stored procedures. When I try to open the report, I get the following error:

    'Cannot join on Memo, OLE, or Hyperlink Object (tblSupport.SupportID=rptSupportJobsExtendedOptions.Expr1)'

    Here is my SQL Statement:

    strSQLStmt = "SELECT rptSupportJobsExtendedOptions.*, tblSupport.FirstName, tblSupport.LastName" & _

    " FROM tblSupport INNER JOIN rptSupportJobsExtendedOptions ON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1" & _

    " WHERE (((rptSupportJobsExtendedOptions.TicketNumber) In (SELECT tblTickets.TicketNumber" & _

    " FROM tblTickets RIGHT JOIN tblTicketAssignments ON tblTickets.TicketNumber = tblTicketAssignments.TicketID" & _

    " WHERE (((tblTicketAssignments.SupportID)=" & [Forms]![frmReportParameterFormSupportDateOpenClose]![cboSupport] & ")"

    I'm sure it's obvious, but rptSupportJobsExtendedOptions is a pass through query and Expr1 is just a string passed from a form to the query as a variable and then spit back out in a column. It is set as a varchar on the SQL side.

    Any ideas of how to make this work? Thanks for your help!

  • Johan Bijnens

    SSC Guru

    Points: 134254

    gomikem (9/12/2008)


    I have some SQL in the code of one of my reports that calls fields from linked (SQL) tables and some pass through queries stored in stored procedures. When I try to open the report, I get the following error:

    'Cannot join on Memo, OLE, or Hyperlink Object (tblSupport.SupportID=rptSupportJobsExtendedOptions.Expr1)'

    Here is my SQL Statement:

    strSQLStmt = "SELECT rptSupportJobsExtendedOptions.*, tblSupport.FirstName, tblSupport.LastName" & _

    " FROM tblSupport INNER JOIN rptSupportJobsExtendedOptions ON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1" & _

    " WHERE (((rptSupportJobsExtendedOptions.TicketNumber) In (SELECT tblTickets.TicketNumber" & _

    " FROM tblTickets RIGHT JOIN tblTicketAssignments ON tblTickets.TicketNumber = tblTicketAssignments.TicketID" & _

    " WHERE (((tblTicketAssignments.SupportID)=" & [Forms]![frmReportParameterFormSupportDateOpenClose]![cboSupport] & ")"

    I'm sure it's obvious, but rptSupportJobsExtendedOptions is a pass through query and Expr1 is just a string passed from a form to the query as a variable and then spit back out in a column. It is set as a varchar on the SQL side.

    Any ideas of how to make this work? Thanks for your help!

    Did your try :

    SELECT rptSupportJobsExtendedOptions.*, tblSupport.FirstName, tblSupport.LastName

    FROM tblSupport

    INNER JOIN rptSupportJobsExtendedOptions

    ON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1

    Where exists ( SELECT *

    FROM tblTickets T

    -- why use the right outer join ???

    inner JOIN tblTicketAssignments A

    ON T.TicketNumber = A.TicketID

    where T.TicketNumber = rptSupportJobsExtendedOptions.TicketNumber )

    " AND tblTicketAssignments.SupportID = " & [Forms]![frmReportParameterFormSupportDateOpenClose]![cboSupport].textvalue & ""

    ... Expr1 is just a string passed ...

    of what datatype ??

    What columns are concatenated to get to your expr1 column ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • gomikem

    SSC-Addicted

    Points: 488

    Sorry it took so long to respond! I tried the following:

    strSQLStmt = "SELECT rptSupportJobsExtendedOptions.*, tblSupport.FirstName, tblSupport.LastName" & _

    "FROM tblSupport" & _

    "INNER JOIN rptSupportJobsExtendedOptions" & _

    "ON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1" & _

    "WHERE EXISTS ( SELECT *" & _

    "FROM tblTickets T" & _

    "INNER JOIN tblTicketAssignments A" & _

    "ON T.TicketNumber = A.TicketID" & _

    "WHERE T.TicketNumber = rptSupportJobsExtendedOptions.TicketNumber )" & _

    "AND tblTicketAssignments.SupportID = " & [Forms]![frmReportParameterFormSupportDateOpenClose]![cboSupport].textvalue & ""

    And got an error that said: Run-time error 438: Object doesn't support this property or method. When I debug, it highlights the query above.

    Also, Expr1 is: @Expr1 VARCHAR(256)

    That variable is passed through using a dynamically created pass-through query and the data is actually just a number. Should it be changed to INT?

  • gomikem

    SSC-Addicted

    Points: 488

    When I changed it to INT and go back to my old query, I get further than I did when it was varchar. The error I mentioned above went away. I will make some more modifications and see if I got it! Thanks for your help with everything. I'll come back and reply how everything worked out!

  • Greg Snidow

    SSCoach

    Points: 16106

    One thing to note. You need to make sure there is a space between the end of each line and the double quote before the & _. Else when Access compiles the lines, they will all run together. Try fixing those, then post back if it still does not work.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Siva Sham

    Grasshopper

    Points: 12

    gomikem - Friday, September 12, 2008 10:14 AM

    I have some SQL in the code of one of my reports that calls fields from linked (SQL) tables and some pass through queries stored in stored procedures. When I try to open the report, I get the following error:

    'Cannot join on Memo, OLE, or Hyperlink Object (tblSupport.SupportID=rptSupportJobsExtendedOptions.Expr1)'

    Here is my SQL Statement: strSQLStmt = "SELECT rptSupportJobsExtendedOptions.*, tblSupport.FirstName, tblSupport.LastName" & _ " FROM tblSupport INNER JOIN rptSupportJobsExtendedOptions ON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1" & _ " WHERE (((rptSupportJobsExtendedOptions.TicketNumber) In (SELECT tblTickets.TicketNumber" & _ " FROM tblTickets RIGHT JOIN tblTicketAssignments ON tblTickets.TicketNumber = tblTicketAssignments.TicketID" & _ " WHERE (((tblTicketAssignments.SupportID)=" & [Forms]![frmReportParameterFormSupportDateOpenClose]![cboSupport] & ")" I'm sure it's obvious, but rptSupportJobsExtendedOptions is a pass through query and Expr1 is just a string passed from a form to the query as a variable and then spit back out in a column. It is set as a varchar on the SQL side.Any ideas of how to make this work? Thanks for your help!

    Hi, 
    today i got same error. finally fixed. 

    1) first linked sql query remove
    2) remove index both field  (tblSupport.SupportID=rptSupportJobsExtendedOptions.Expr1)
    3) check also both field same data type like nvarchar(100)
    3) after reattach query
    4) run this query will work
    5)if work then you can add index again

    Thanks & Regards
    Siva
    Srivilliputtur
    www.friendlylearn.com

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply