SQL - problems editing

[ghh3rd]

The following SQL is from the SQL view in the Access QBE grid and works OK.

SELECT Count([date closed]) AS Total, (Select count(WinID) from (SELECT qryAdHoc.*, * FROM qryAdHoc WHERE format (qryAdHoc.[Date Closed],"mm")= "11" and format (qryAdHoc.[Date Closed],"yyyy")= "2008" )) AS Won
FROM [SELECT qryAdHoc.*, * FROM qryAdHoc WHERE (((qryAdHoc.[Date Closed]) Between #4/1/2008# And #12/31/2008#))]. AS [%$##@_Alias];

When I change the " (quote marks) to ' (apostrophe marks) so I can use this in VBA, I get:

Invalid Bracketing of name 'SELECT qryAdHoc.*, * FROM qryAdhoc WHERE (((qryAdHoc.([DateClosed'.

It seems that anything that I do to this SQL, inclusing creating one long SQL string from it results in the same message Even removing and then immediately reinserting a parenthesis causes the message.

Can anyone tell me what I'm doing wrong?

Thanks in advance,



Granville

 

[ByteMyzer]

Try:

Dim sSQL As String

sSQL = "SELECT Count([date closed]) AS Total, " _
     & "    (Select count(WinID)" _
     & "     from (SELECT qryAdHoc.*" _
     & "           FROM qryAdHoc" _
     & "           WHERE format (qryAdHoc.[Date Closed],'mm')= '11'" _
     & "           and format (qryAdHoc.[Date Closed],'yyyy')= '2008'" _
     & "          )" _
     & "    ) AS Won" _
     & " FROM (SELECT qryAdHoc.*" _
     & "       FROM qryAdHoc" _
     & "       WHERE (qryAdHoc.[Date Closed]" _
     & "       Between #4/1/2008# And #12/31/2008#)" _
     & "      ) AS [%$##@_Alias];"

Additional Note: The part of your SQL statement that is causing you trouble is:
FROM [SELECT qryAdHoc.*, * FROM qryAdHoc WHERE (((qryAdHoc.[Date Closed]) Between #4/1/2008# And #12/31/2008#))]. AS [%$##@_Alias];

If you change it to:
FROM (SELECT qryAdHoc.*, * FROM qryAdHoc WHERE (((qryAdHoc.[Date Closed]) Between #4/1/2008# And #12/31/2008#))) AS [%$##@_Alias];

...it should fix the problem.