End Of Statement Error

[Medea]

Hello All,

I have an SQL statement behind a report that is giving me an "Expected; End of Statement Error". I can put the first part (blue) in a separate query and it runs fine. When I add the rest of the statement behind the report (Red), the errorr message selects "Yes". The report has a chart in it, that's last part of the statement. I had help getting this far, but know I am ready to pull my hair out! I am assuming I have either too many ((( or not enough )))).

strRowSource = "SELECT ResRat_Text, Count(ResRat_Text) AS CountOfResRat_Text " & _
"From(" & _"SELECT tbl_Questions.Q_ID, tbl_Questions.Q_Text, tbl_RespTextRating.ResRat_Text, tbl_Questions.Active, tbl_Main.Eval_Type, tbl_Main.Rev_Date" & _
"FROM tbl_Main INNER JOIN (tbl_RespTextRating INNER JOIN (tbl_Questions INNER JOIN tbl_RespRating ON tbl_Questions.Q_ID=tbl_RespRating.Q_ID) ON tbl_RespTextRating.ResRat_ID=tbl_RespRating.Response) ON tbl_Main.Eval_ID=tbl_RespRating.Eval_ID" & _
"WHERE (((tbl_Questions.Active)="Yes") And ((tbl_Main.Eval_Type)=1) And ((tbl_Main.Rev_Date) Between forms!frm_ParRatRpt!txt_Start And forms!frm_ParRatRpt!txt_End))" & _
"AS SubSelect " & _
"WHERE " & Me!chtColorChart.LinkChildFields & " = '" & _
Me(Me!chtColorChart.LinkMasterFields) & "' GROUP BY [Res_Text]"
Set rsRowSourceFiltered = CurrentDb. _
OpenRecordset(strRowSource)
Debug.Print strRowSource


Any ideas would be greatly appreciated!

Trish

 

[jdraw]

Hello All,

I have an SQL statement behind a report that is giving me an "Expected; End of Statement Error". I can put the first part (blue) in a separate query and it runs fine. When I add the rest of the statement behind the report (Red), the errorr message selects "Yes". The report has a chart in it, that's last part of the statement. I had help getting this far, but know I am ready to pull my hair out! I am assuming I have either too many ((( or not enough )))).

strRowSource = "SELECT ResRat_Text, Count(ResRat_Text) AS CountOfResRat_Text " & _
"From(" & _"SELECT tbl_Questions.Q_ID, tbl_Questions.Q_Text, tbl_RespTextRating.ResRat_Text, tbl_Questions.Active, tbl_Main.Eval_Type, tbl_Main.Rev_Date" & _
"FROM tbl_Main INNER JOIN (tbl_RespTextRating INNER JOIN (tbl_Questions INNER JOIN tbl_RespRating ON tbl_Questions.Q_ID=tbl_RespRating.Q_ID) ON tbl_RespTextRating.ResRat_ID=tbl_RespRating.Response) ON tbl_Main.Eval_ID=tbl_RespRating.Eval_ID" & _
"WHERE (((tbl_Questions.Active)="Yes") And ((tbl_Main.Eval_Type)=1) And ((tbl_Main.Rev_Date) Between forms!frm_ParRatRpt!txt_Start And forms!frm_ParRatRpt!txt_End))" & _
"AS SubSelect " & _
"WHERE " & Me!chtColorChart.LinkChildFields & " = '" & _
Me(Me!chtColorChart.LinkMasterFields) & "' GROUP BY [Res_Text]"
Set rsRowSourceFiltered = CurrentDb. _
OpenRecordset(strRowSource)
Debug.Print strRowSource


Any ideas would be greatly appreciated!

Trish

I'd start by putting this on a single line, or insert an &

Set rsRowSourceFiltered = CurrentDb. _
OpenRecordset(strRowSource)

Set rsRowSourceFiltered = CurrentDb.OpenRecordset(strRowSource)

 

[Zaeed]

Can you post your db...

 

[Medea]

Thanks for the response but I just fugured it out. I took out all the "WHERE" clauses except the date parameters and it runs great.

Have a great night!

Trish