Query In String Variable (1 Viewer)

[mfm]

SELECT ProductGrp.Product AS ProductName, (SELECT TOP 1 ProductSale.Values FROM ProductSale WHERE (((ProductSale.Types)=""3"") and (ProductSale.ProductID=ProductGrp.ID)  AND ((ProductSale.Period)=[Month]));) AS JUThisYear, ((SELECT TOP 1 ProductSale.Values  FROM ProductSale WHERE (((ProductSale.Types)=""1"") And (ProductSale.ProductID=ProductGrp.ID) AND ((ProductSale.Period)=[Month])))) AS NTSThisYear, (SELECT TOP 1 ProductSale.Values FROM ProductSale WHERE (((ProductSale.Period)=[Month]) AND ((ProductSale.Types)=""2"") AND ((ProductSale.ProductID)=[ProductGrp].[ID]))) AS MKTThisYear, (SELECT TOP 1 ProductSale.Values FROM ProductSale WHERE (((ProductSale.Period)=DateAdd(""yyyy"",-1,[Month])) And ((ProductSale.Types)=""1"") And ((ProductSale.ProductID)=ProductGrp.ID))) AS NTSLastYear, (SELECT TOP 1 ProductSale.Values FROM ProductSale WHERE (((ProductSale.Period)=DateAdd(""yyyy"",-1,[Month])) And (( ProductSale.Types)=""2"") And ((ProductSale.ProductID)=ProductGrp.ID))) AS MKTLastYear, (SELECT Sum(ProductSale.Values) AS SumOfValues FROM ProductSale WHERE (((ProductSale.ProductID)=ProductGrp.ID) And ((ProductSale.Types)=""3"") And ((ProductSale.Period)>=#1/27/2009# And (ProductSale.Period)<=[Month]))) AS JUThisYTD, (SELECT Sum(ProductSale.Values) AS SumOfValues FROM ProductSale WHERE (((ProductSale.ProductID)=[ProductGrp].[ID]) AND ((ProductSale.Types)=""1"") AND ((ProductSale.Period)>=#1/27/2009# And (ProductSale.Period)<=#1/27/2009#))) AS NTSThisYTD, (SELECT Sum(ProductSale.Values) AS SumOfValues FROM ProductSale WHERE (((ProductSale.ProductID)=[ProductGrp].[ID]) AND ((ProductSale.Types)=""2"") AND ((ProductSale.Period)>=#1/27/2009# And (ProductSale.Period)<=#1/27/2009#))) AS MKTThisYTD, (SELECT Sum(ProductSale.Values) AS SumOfValues FROM ProductSale WHERE (((ProductSale.Types)=""1"") AND ((ProductSale.ProductID)=[ProductGrp].[ID]) AND ((ProductSale.Period)>=DateSerial(DatePart(""yyyy"",DateAdd(""yyyy"",-1,[Month])),1,1) And (ProductSale.Period)<=[Month]))) AS NTSLastYTD, (SELECT Sum(ProductSale.Values) AS SumOfValues FROM ProductSale WHERE (((ProductSale.Types)=""2"") AND ((ProductSale.ProductID)=[ProductGrp].[ID]) AND ((ProductSale.Period)>=DateSerial(DatePart(""yyyy"",DateAdd(""yyyy"",-1,[Month])),1,1) And (ProductSale.Period)<=[Month])) ) AS MKTLastYTD, ([NTSThisYTD]/[JUThisYTD])*100 AS AchPer, (([NTSThisYTD]-[NTSLastYTD])/[NTSLastYTD])*100 AS GrowthNTS, (([MKTThisYTD]-[MKTLastYTD])/[MKTLastYTD])*100 AS GrowthMKT, (Select Top 1 ProductSale.Period From ProductSale) AS Tereekh, ProductGrp.MajorGrp, ProductGrp.MinorGrp, ProductGrp.Product, ProductGrp.ID, (SELECT TOP 1 Month([ProductSale].[Period]) AS MonthValue FROM ProductSale) AS MonthValue, (SELECT TOP 1 Year([ProductSale].[Period]) AS YearValue FROM ProductSale) AS YearValue INTO TBL_Temp1
FROM ProductGrp;

how can i put the above whole query into the string variable. i want to initialize and then call docmd.runsql strVariable but when i copy the query and paste into initialize command its behave strange. i attach a screenshot to u guys what happen i copy/paste the query between ""

 

[aziz rasul]

You need to put the SQL string within quotes. For example

strSQL = "SELECT ..... "
DoCmd.RunSQL strSQL

 

[!Blue]

And if you want it to look neat...

Dim sql as string
 
sql = _
   "SELECT ProductGrp.Product AS ProductName, " & _
   "(SELECT TOP 1 ProductSale.Values FROM ProductSale WHERE (((ProductSale.Types)=""3"") " & _
   " and ...."

 

[mfm]

@!Blue
Thanks its working successfully