1

My job has tools that require us to mass upload data through Excel workbooks, so I'm trying to embed my SQL results into my files to save me a step. I've created the data connection, but haven't quite figured out the code format in the 'Definition' box yet.

Right now I have:

select custcontnum from "CONTACT"."dbo"."Incont" where custcode='SNH' and username = '[Custcontnums$G2]'

But it's pulling back blank. (If I define the username criteria itself between single quotes, it does work) Eventually I would like to create an "in" statement for the username criteria, so something like:

select custcontnum from "CONTACT"."dbo"."Incont" where custcode='SNH' and username in ('[Custcontnums$G2]','[Custcontnums$G3]','[Custcontnums$G4]')

What is the correct format for an excel reference in the data connection feature for Excel?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Rachel M
  • 11
  • 1
  • Because it is a string you must exclude the code parts of the string using the & concatenation. For example `"select custcontnum from CONTACT.dbo.Incont where custcode='SNH' and username = '"&[Custcontnums$G2]&"'"` – Scott Craner Jan 19 '16 at 15:55
  • Hm, if I try that, I get an "Incorrect syntax near '&' " error. I verified that there is data in cell G2. – Rachel M Jan 19 '16 at 16:18
  • Can you show more of the code you are using? – Scott Craner Jan 19 '16 at 16:20
  • This might be a duplicate to the following: http://stackoverflow.com/questions/1285686/excel-use-a-cell-value-as-a-parameter-for-a-sql-query or the following: http://superuser.com/questions/197453/run-an-sql-query-with-a-parameter-from-excel-2007 – Ralph Jan 19 '16 at 16:24
  • Unfortunately that's it. I'm looking to reference a specific username and pull back that user's unique ID in the database. The incorrect syntax error appears when I plug in this code: "select custcontnum from CONTACT.dbo.Incont where custcode='SNH' and username = '"&[Custcontnums$G2]&"'" It's hung up on the first ampersand, but the formatting appears to be correct. – Rachel M Jan 19 '16 at 16:28
  • Thank you for the articles! I had a hard time finding this question in the site. – Rachel M Jan 19 '16 at 16:29

0 Answers0