September 12, 2008 at 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!
September 12, 2008 at 10:47 am
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
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
September 16, 2008 at 10:56 am
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?
September 16, 2008 at 10:58 am
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!
September 16, 2008 at 11:17 am
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.
January 5, 2018 at 11:51 am
gomikem - Friday, September 12, 2008 10:14 AMI 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply