'Cannot join on Memo, OLE, or Hyperlink Object' Error
-
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!
-
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.LastNameFROM tblSupportINNER JOIN rptSupportJobsExtendedOptionsON tblSupport.SupportID = rptSupportJobsExtendedOptions.Expr1Where exists ( SELECT *FROM tblTickets T-- why use the right outer join ???inner JOIN tblTicketAssignments AON T.TicketNumber = A.TicketIDwhere 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
Learn to play, play to learn !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 but most of the time this is me
-
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?
-
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!
-
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. - gomikem - Friday, September 12, 2008 10:14 AM
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 againThanks & Regards
Siva
Srivilliputtur
www.friendlylearn.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply