SQL in VBA Macro

Question

I have a VBA macro, and i wish to add the following SQL,,however being a newbie dont know how to break the query in the excel macro?,,this the query:

strSQL = "select (

 SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS NUMBER_OF_ACCOUNTS_NOT_DELETED FROM       CUSTOMER_ACCOUNT INNER JOIN
ACCOUNT ON CUSTOMER_ACCOUNT.ACCOUNT_ID=ACCOUNT.ID
 WHERE
Convert(datetime,convert(char(10),[CUSTOMER_ACCOUNT].CREATED_ON,101))
BETWEEN '2009-01-01' AND '2009-12-31' AND  CUSTOMER_ACCOUNT.DELETED!='1' AND      ACCOUNT.DELETED !='1'
)
-
(SELECT    COUNT (DISTINCT dLOAD_ACCOUNT_DETAIL.ACCOUNT_NUMBER) AS NOT_ACTIVE_ACCOUNTS

FROM         dbo.LOAD_ACCOUNT_DETAIL LEFT OUTER JOIN
                  ACCOUNT ON dbo.LOAD_ACCOUNT_DETAIL_0.ID = dbo.ACCOUNT.ID WHERE
ACCOUNT_STATUS !='1') AS DIFFERENCE 

buting the whole thing in quotes dont work...!

Answer 1

Dim myString As String

myString = "You can " & _
    "use '& _' to concatenate " & _
    "strings over multiple lines."

Answer 2

Depending on how you are running your query, you often have to break your query up into smaller chunks (less than ~200 characters, I forget exact amount).

This is done by breaking it into an array of strings:

Either:

QueryArry = Array("Your ","Query ","here ")

Using Marg's method this becomes:

QueryArry = Array("Your ", _
    "Query ", _
    "here ")

Or you can do it like this:

Dim QueryArry(0 to 100) as String
QueryArry(0)="Your "
QueryArry(1)="Query "
QueryArry(2)="Here "

WARNING: In every case make sure to add a space before the end of each quote... because these lines get appended together and without the extra space would be "YourQueryHere" Instead of "Your Query Here".

Dan

Answer 3

You can also break it up like this:

strSQL = "select ( "
strSQL = strSQL & "SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS NUMBER_OF_ACCOUNTS_NOT_DELETED FROM       CUSTOMER_ACCOUNT INNER JOIN "
strSQL = strSQL & "ACCOUNT ON CUSTOMER_ACCOUNT.ACCOUNT_ID=ACCOUNT.ID "
...

I prefer this way to the strSQL = "..." & _ "..." & _ "..." over multiple lines, but potato / potato...