SQL in VBA Function

Question

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

1. what, exactly, do you mean by it doesn't work? 2. I'm not sure your SQL is right in the first place. That statement doesn't make much sense to me. — moleboy
– moleboy, Commented Feb 22, 2010 at 19:55

Dan · Accepted Answer · 2010-02-22 22:56:16Z

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

edited Feb 22, 2010 at 22:56

answered Feb 22, 2010 at 21:49

Dan

10.2k15 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

andreas Over a year ago

okkk.....but how do i execute the array?.. with one query i used this Set rsData = objConn.Execute(strSQL)..now with the array? using this method: Dim QueryArry(0 to 100) as String QueryArry(0)="Your " QueryArry(1)="Query " QueryArry(2)="Here "

Dan Over a year ago

Yes. Set rsData = objConn.Execute(QueryArry)... though it depends on the rest of your code, but that should work. If your getting strange error messages from the SQL, try running the SQL in another application... Excel doesn't give informative error messages, other programs will tell you where in the code there is a problem.

marg · Accepted Answer · 2010-02-22 19:28:32Z

1

Dim myString As String

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

answered Feb 22, 2010 at 19:28

marg

2,8251 gold badge31 silver badges34 bronze badges

1 Comment

barrowc Over a year ago

+1 there is an upper limit on the number of concatenations - somewhere between 20 and 30 - in a single statement so you might have to build up a very long statement in stages

Craig · Accepted Answer · 2010-02-26 19:30:01Z

1

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

answered Feb 26, 2010 at 19:30

Craig

5682 gold badges5 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL in VBA Function

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related