SQL concatenation inside VBA string

Question

I'm using VBA excel 2003,SQL 2005 to make a sql query call and inside my sql statement I'm using '+' operator to concatenate two strings.

dim query as string
query =  "Select distinct ', '+emailaddress1 "
query = query & "from contact "

would this work inside vba? My query returns too many records in excel but not in SQL?

Please just focus on this 2 lines of code and not worry about the rest of my sql call, I'm just wondering whether or not this specific string would work?

Are you trying to concatenate a prefix comma to each email address? — Daniel Vassallo, Sep 28 '10 at 23:27
Yea I'm trying to make emails look like "b@a.com, c@d.com" .... — EKet, Sep 28 '10 at 23:29

score 0 · Accepted Answer · answered Sep 28 '10 at 23:46

Your code will return a column where each row would be an email address with a comma in front of it. If that is what you want, then yes, it will work.

If, on contrary, you want a single string where all email addresses would be listed, separated with commas, that'd be

query =         "declare @foo varchar(max);"
query = query & "select distinct @foo = isnull(@foo,'') + emailaddress1 + ', ' from contact;"
query = query & "select left(@foo, len(@foo)-2);"

Thanks for the straight answer...it was merely a syntactical question :) — EKet, Sep 28 '10 at 23:49

SQL concatenation inside VBA string

1 Answers1