Convert Access SQL code to usable ADO SQL?

[XJ900]

I am trying to convert this code from an MS Access table to code that will be usable with an ADO connection. But I've had problems trying to figure out how to select multiple tables, inner join relationships and summing up fields from the RS.Open statement.

SELECT Sites.SiteID, [To Date] AS Weekend, Items.ItemID, Sum(Salesmix.TotalNumberSold) AS SumOfTotalNumberSold, Sum(Salesmix.NetSales) AS SumOfNetSales
FROM Sites INNER JOIN (Items INNER JOIN Salesmix ON Items.ItemNumber = Salesmix.ItemNumber) ON Sites.SiteNumber = Salesmix.SiteNumber
WHERE (((Salesmix.BusinessDate) Between [From Date] And [To Date]))
GROUP BY Sites.SiteID, [To Date], Items.ItemID, [From Date], Salesmix.SiteNumber, Salesmix.ItemNumber
HAVING (((Items.ItemID)="4829" Or (Items.ItemID)="3284" Or (Items.ItemID)="3282" Or (Items.ItemID)="14544" Or (Items.ItemID)="14511" Or (Items.ItemID)="10619" Or (Items.ItemID)="4257" Or (Items.ItemID)="14019" Or (Items.ItemID)="11553" Or (Items.ItemID)="13299" Or (Items.ItemID)="14102" Or (Items.ItemID)="11380" Or (Items.ItemID)="5769" Or (Items.ItemID)="10091" Or (Items.ItemID)="5750" Or (Items.ItemID)="5740" Or (Items.ItemID)="11379" Or (Items.ItemID)="10093" Or (Items.ItemID)="5756" Or (Items.ItemID)="10582" Or (Items.ItemID)="5743"));

 

[JerryKlmns]

Seems to be a parameter query.
So, either you feed (properly) the values of [From Date], [To Date] parameters to the sql on the fly, or take a look on ADODB.Command thread705-1244830

 

[BobRodes]

You haven't described your trouble, but I would set up the join this way:

SELECT S.SiteID, [To Date] AS Weekend, I.ItemID, Sum(Sm.TotalNumberSold) AS SumOfTotalNumberSold, Sum(Sm.NetSales) AS SumOfNetSales
FROM Sites s
JOIN SalesMix on s.SiteNumber = sm.SiteNumber
JOIN Items i ON i.ItemNumber = sm.ItemNumber
WHERE [etc]