[pierrevbaexcel]

All of us have experienced MAJOR problems trying to fit SQL sentences into a VBA piece of code.Here is how I solve the problem.

Here is the original SQL sentence from the SQL generator:

Select LH.location_cd , LH.location_name , LH.business_address_1 , LH.business_address_2 , LH.business_city,
LH.business_state , LH.business_zip , LH.market_cd , LH.market_name,
CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END as FunCem , LH.Product_Line_Cd ,
CASE WHEN EXISTS (select business_entity_id from dbo.TBL_COMBO_LOCATION
where business_entity_id = BE.business_entity_id or combo_business_entity_id = BE.business_entity_id)
THEN 'Combo' ELSE 'St-Alone' END as Combo_Cd from dbo.Location_Hierarchy_Flat as LH
inner join tbl_business_entity as BE
ON LH.location_cd = BE.location_number
where LH.Status_code = 'ACTV'
and LH.Product_Line_Cd IN ('FH','CE')
and LH.Business_Line_Cd IN ( 'CEM', 'FUNHM', 'DCP' )
order by LH.Business_Line_Cd, LH.Location_cd

In this example I do not break the sentence as each line fits well into my VBA code window.

Basically what I do is concatenate all the lines in a single variable.

Step 1:I create partial lines of code the first line being varSQL = "" and a certain number of other lines reading varSQL = varSQL & ""

varSQL = ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""
varSQL = varSQL & ""

Step 2: Then I insert each line of the SQL sentence Betwwen the quotation marks

Step 3: AND, AND, AND, AND I add a space after the opening quotation makk for all the lines starting with the second one.

and here is the SQL sentence within a VBA variable

varSQL = "Select LH.location_cd , LH.location_name , LH.business_address_1 , LH.business_address_2 , LH.business_city,"
varSQL = varSQL & " LH.business_state , LH.business_zip , LH.market_cd , LH.market_name,"
varSQL = varSQL & " CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END as FunCem , LH.Product_Line_Cd ,"
varSQL = varSQL & " CASE WHEN EXISTS (select business_entity_id from dbo.TBL_COMBO_LOCATION "
varSQL = varSQL & " where business_entity_id = BE.business_entity_id or combo_business_entity_id = BE.business_entity_id)"
varSQL = varSQL & " THEN 'Combo' ELSE 'St-Alone' END as Combo_Cd from dbo.Location_Hierarchy_Flat as LH"
varSQL = varSQL & " inner join tbl_business_entity as BE"
varSQL = varSQL & " ON LH.location_cd = BE.location_number"
varSQL = varSQL & " where LH.Status_code = 'ACTV'"
varSQL = varSQL & " and LH.Product_Line_Cd IN ('FH','CE')"
varSQL = varSQL & " and LH.Business_Line_Cd IN ( 'CEM', 'FUNHM', 'DCP' )"
varSQL = varSQL & " order by LH.Business_Line_Cd, LH.Location_cd"

and I use this variable:

.CommandText = varSql

See more on Excel in www.excel-examples.com

See more on VBA for Excel in www.excel-vba.com

[savbill]

Thats a good way of doing it. For even more concise concatonation you can do the same with the line continuation "& _" then your don't have to repeat the variable name on each line. Looks like this.

strSQL ="Select LH.location_cd , LH.location_name , LH.business_address_1 ," & _
"LH.business_address_2 , LH.business_city, " & _
"LH.business_state , LH.business_zip , LH.market_cd , LH.market_name," & _
"CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END" & _
" as FunCem , LH.Product_Line_Cd ," & _
"CASE WHEN EXISTS (select business_entity_id from " & _
"dbo.TBL_COMBO_LOCATION" & _
"where business_entity_id = BE.business_entity_id or " & _
"combo_business_entity_id = BE.business_entity_id)"

For some larger projects I create a table of SQL statements with an ID for each, then use a function to get the SQL from the table 'fGetSQL(sqlID)'. That eliminates having to concatonate. You can alter the SQl fairly quickly. If you embed tags for variables {strVar} then use string replace on the SQL to swap out the Tags for your variables. Which makes this technique very versital. Can do this in Access also.

[pierrevbaexcel]

SavBill

One thing I forgot to tell the readers is that this concatenation or continuation thing must be done outside the Visual Basic Editor because the VBA does a loosy job with SQL statements when one tries to insert them in his VBA procedure. It must be done in Notepad or Word or in a text box in excel then copied/pasted into the VBE.

This tip I am sharing is for users who are not experts at SQL like me. I get Access, Microsoft Query or a good friend of mine to write the statements and I use them in VBA.

Although I also use dynamic values in my queries the second part of your message in out of my league.