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?

Answer 1

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);"