SQL in VBA Function

I have a VBA Function, and I want to add the following SQL, however being a newbie I don't know how to break the query in the code.

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 th开发者_JS百科e whole thing in quotes dont work...!

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

Dim myString As String

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

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...