Nested Query Error

[vix666]

I have a nested query and it comes up with an error saying "Run-Time error 3131, Syntax error in FROM Clause"
and i keep looking at my code and I just dont know whats wrong.
So any help would be much appreciated.

strSql = "SELECT Count(Counter) AS CountOfCounter, [Type of Complaint], Format([Date],""mm/yyyy"&quot AS Month" & _

" FROM (SELECT [Complaints Table].Counter, [Complaints Table].[Type of Complaint], [Complaints Table].Date, [Complaints Table].[Attributable to Company]" & _

" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between #" & _
Forms![Print Request between dates]!sdate & "# And # " & _
Forms![Print Request between dates]!edate & "#)) " & _
" AND (([Complaints Table].[Attributable to Company])=""epco"&quot" & _
" ORDER BY [Complaints Table].[Type of Complaint], [Complaints Table].Date" & _
" GROUP BY [Type of Complaint], Format([Date],""mm/yyyy"&quot);"

 

[Page410]

What is the epco that is listed in the subquery? If it is a variable of some kind you should be separating it from the sql with ampersands. I don't know it that is your issue or not but it's the first thing that I saw.

strSql = "SELECT Count(Counter) AS CountOfCounter, [Type of Complaint], Format([Date],""mm/yyyy"&quot AS Month" & _

" FROM (SELECT [Complaints Table].Counter, [Complaints Table].[Type of Complaint], [Complaints Table].Date, [Complaints Table].[Attributable to Company]" & _
" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between #" & _
Forms![Print Request between dates]!sdate & "# And # " & _
Forms![Print Request between dates]!edate & "#)) " & _
" AND (([Complaints Table].[Attributable to Company])='" & epco & "')" & _
" ORDER BY [Complaints Table].[Type of Complaint], [Complaints Table].Date" & _
" GROUP BY [Type of Complaint], Format([Date],""mm/yyyy"&quot);"

 

[vix666]

Thanks for the help but is not a variable it is search criteria for the query.

Any other suggestions?

Thanks

Vicky

 

[JonFer]

Why are you nesting? Can't you do this with a simple query?

SELECT Count(Counter) AS CountOfCounter,
[Type of Complaint],
Format([Date],"mm/yyyy&quot AS Month

FROM [Complaints Table]

WHERE ( ([Complaints Table].Date Between
Forms![Print Request between dates]!sdate
AND Forms![Print Request between dates]!edate)
AND ([Complaints Table].[Attributable to Company]="epco&quot )

GROUP BY [Type of Complaint],
Format([Date],"mm/yyyy&quot

ORDER BY [Type of Complaint],
Format([Date],"mm/yyyy&quot AS Month

 

[vix666]

Thanks, I never thought of doing it that way.
I have redone it but I get an error saying "Missing operator in query expression 'Format([Date],""mm"&quot AS Month'. Any ideas what is missing?

Many Thanks

strSQL = "SELECT Count([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot AS Month" & _
" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between " & _
" [Forms]![Print Request between dates]![sdate]" & _
" And [Forms]![Print Request between dates]![edate])" & _
" AND (([Complaints Table].[Attributable to Company])=""epco"&quot)" & _

" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _

" ORDER BY [Type of Complaint], Format([Date],""mm"&quotAs Month; "

 

[DeanWilliams]

Shouldn't there only be one set of quotes around 'mm'?

Format([Date],"mm") AS Month

Dean.

 

[vix666]

Thanks for your help but I have already sorted this problem, I needed to remove the format part from the Order By clause.

I now have another error saying too few parameters expected, any ideas?

Thanks

Vicky


strSQL = "SELECT Count([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot AS Month" & _
" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between " & _
" [Forms]![Print Request between dates]![sdate]" & _
" And [Forms]![Print Request between dates]![edate])" & _
" AND (([Complaints Table].[Attributable to Company])=""epco"&quot)" & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _
" ORDER BY [Type of Complaint], Month; "