problem with query in vba

  • Thread starter Thread starter tw
  • Start date Start date
T

tw

I have this query in code

strSQLp = "PARAMETERS [forms]![frmqryonedateparameter]![txtDate] Date; "
strSQLp = strSQLp & "SELECT qryWeeklyVisitsUnion.cert,
qryWeeklyVisitsUnion.tov, qryWeeklyVisitsUnion.dos, "
strSQLp = strSQLp & "qryWeeklyVisitsUnion.ini ,
WorkSpaceDoctorsOrders.[DO-FK Cert Period id], "
strSQLp = strSQLp & "WorkSpaceDoctorsOrders.[DO-FK Type of Visit] "
strSQLp = strSQLp & "FROM qryWeeklyVisitsUnion LEFT JOIN
WorkSpaceDoctorsOrders "
strSQLp = strSQLp & "ON (qryWeeklyVisitsUnion.cert =
WorkSpaceDoctorsOrders.[DO-FK Cert Period id]) "
strSQLp = strSQLp & "AND (qryWeeklyVisitsUnion.tov =
WorkSpaceDoctorsOrders.[DO-FK Type of Visit]) "
strSQLp = strSQLp & "WHERE (((WorkSpaceDoctorsOrders.[DO-FK Cert Period id])
Is Null) And "
strSQLp = strSQLp & "((WorkSpaceDoctorsOrders.[DO-FK Type of Visit]) Is
Null)) "
strSQLp = strSQLp & "ORDER BY qryWeeklyVisitsUnion.cert,
qryWeeklyVisitsUnion.tov;"

Set rsp = CurrentDb.OpenRecordset(strSQLp)

when it gets to the set rsp statement, I get an error that the query is too
complex
if I do ?strsqlp in the immediate window I get the following

PARAMETERS [forms]![frmqryonedateparameter]![txtDate] DateTime;
SELECT qryWeeklyVisitsUnion.cert, qryWeeklyVisitsUnion.tov,
qryWeeklyVisitsUnion.dos, qryWeeklyVisitsUnion.ini,
WorkSpaceDoctorsOrders.[DO-FK Cert Period id], WorkSpaceDoctorsOrders.[DO-FK
Type of Visit]
FROM qryWeeklyVisitsUnion LEFT JOIN WorkSpaceDoctorsOrders ON
(qryWeeklyVisitsUnion.cert = WorkSpaceDoctorsOrders.[DO-FK Cert Period id])
AND (qryWeeklyVisitsUnion.tov = WorkSpaceDoctorsOrders.[DO-FK Type of
Visit])
WHERE (((WorkSpaceDoctorsOrders.[DO-FK Cert Period id]) Is Null) AND
((WorkSpaceDoctorsOrders.[DO-FK Type of Visit]) Is Null))
ORDER BY qryWeeklyVisitsUnion.cert, qryWeeklyVisitsUnion.tov;

if I paste this into the sql view of a new query I get the same message
while the code is still open, but if I stop the debugger the pasted sql
statement works fine. I have the following at the top of my procedure
Dim strSQLp As String
Dim rsp As DAO.Recordset

I have microsoft dao 3.6 object library checked and have other sql
statements (different vars) that are working without problems in the same
procedure.
msAccess2002

Thanks for all the help I can get.
 
I solved the problem by redoing the sql statement in code using tables
instead of the saved queries. My guess is since the parameters required
were in one of the queries used rather than the sql statement in code, the
value was not carried over. However, I don't know why that would be since
the form is open at the time and the data should have been available to the
query.
 
Back
Top