SQL Error in VBA

Question

Hey guys!

I have the following code in SQL (2005) which calculates the avarage user logins on a systm:

   with 
  user_total as 
  (
  select COUNT(distinct ID) as counter 
   FROM [dbo].[LOG]
   where [LOG].DESCRIPTION='Login success.' 
         AND  
     Convert(datetime,convert(char(10),[LOG].CREATED_ON,101)) BETWEEN '2009-01- 01'         AND '2009-12-31'

      ),
      USER_avg as
      (
     select  counter/365   as Avarage_Daily_Logins
     from user_total 
     )

      select *
     from USER_avg

Now the problem is when i put this in a VBA macro in excel to get the result in a spcific cell in strSQL = "QUERY SHOWN ABOVE HERE" argument i get the error in excel

incorrect sysntax near the keyword with

Its worth mentioning that i dont break the code in VBA in multiple lines..i have it all in one line.

Answer 1

Use ;WITH ...

WITH usage for a CTE must have ; after the previous statement. To ensure this is the cases, prefix with ;

Answer 2

Check all of your spacings, if there is an error further down the compiler may interpret it as a problem with the with statement.