CODEWITHSUNDEEP
excelvba
andreas
andreas

Reputation: 207

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 the whole thing in quotes dont work...!

Upvotes: 1

Views: 1283

Answers (3)

Craig
Craig

Reputation: 558

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

Upvotes: 1

Dan
Dan

Reputation: 10171

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

Upvotes: 2

marg
marg

Reputation: 2827

Dim myString As String

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

Upvotes: 1

Related Questions