I have 2 queries. The first returns multiple rows for each record I want to report on. The second query is more or less the same but without the multiple rows. In the second query I concatenate the data I want from the multiple rows into 1 field in the second query. This works. If I hard code some criteria into the first query this again works. If I try and pick up the criteria for the first query from a form then this query returns the correct data but if I try and run the second query I get the error in the subject.
The SCL for the pair that work are
SELECT StockLocation.StockLocationDescription, StockLocation.StockLocation, [Component Parts].[Component Part ID] AS CPID, [Component Parts].[Component Part No], StockLocation_1.StockLocation AS StockLoc, StockLocation.StockLocation AS SL
FROM StockLocation INNER JOIN ((([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) INNER JOIN PartsStockLocation AS PartsStockLocation_1 ON [Component Parts].[Component Part ID] = PartsStockLocation_1.CPartsID) INNER JOIN StockLocation AS StockLocation_1 ON PartsStockLocation_1.StockLocationID = StockLocation_1.ID) ON StockLocation.ID = PartsStockLocation.StockLocationID
WHERE (((StockLocation.StockLocation)="R1"));
SELECT StockLocation.StockLocation, [Component Parts].[Component Part No], [Component Parts].[Quantity in Stock], ConcatRelated("[StockLoc]","[QuantityInStock]","[CPID] = " & [Component Parts].[Component Part id]) AS OtherLocation
FROM StockLocation INNER JOIN ([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) ON StockLocation.ID = PartsStockLocation.StockLocationID;
The pair that do not work (and only the where should be different) are
SELECT StockLocation.StockLocationDescription, StockLocation.StockLocation, [Component Parts].[Component Part ID] AS CPID, [Component Parts].[Component Part No], StockLocation_1.StockLocation AS StockLoc, StockLocation.StockLocation AS SL
FROM StockLocation INNER JOIN ((([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) INNER JOIN PartsStockLocation AS PartsStockLocation_1 ON [Component Parts].[Component Part ID] = PartsStockLocation_1.CPartsID) INNER JOIN StockLocation AS StockLocation_1 ON PartsStockLocation_1.StockLocationID = StockLocation_1.ID) ON StockLocation.ID = PartsStockLocation.StockLocationID
WHERE (((StockLocation.StockLocation)=[forms]![Dates for stock reports]![txtStockLoc]));
SELECT StockLocation.StockLocation, [Component Parts].[Component Part No], [Component Parts].[Quantity in Stock], ConcatRelated("[StockLoc]","[QuantityInStock]","[CPID] = " & [Component Parts].[Component Part id]) AS OtherLocation
FROM StockLocation INNER JOIN ([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) ON StockLocation.ID = PartsStockLocation.StockLocationID;
The SCL for the pair that work are
SELECT StockLocation.StockLocationDescription, StockLocation.StockLocation, [Component Parts].[Component Part ID] AS CPID, [Component Parts].[Component Part No], StockLocation_1.StockLocation AS StockLoc, StockLocation.StockLocation AS SL
FROM StockLocation INNER JOIN ((([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) INNER JOIN PartsStockLocation AS PartsStockLocation_1 ON [Component Parts].[Component Part ID] = PartsStockLocation_1.CPartsID) INNER JOIN StockLocation AS StockLocation_1 ON PartsStockLocation_1.StockLocationID = StockLocation_1.ID) ON StockLocation.ID = PartsStockLocation.StockLocationID
WHERE (((StockLocation.StockLocation)="R1"));
SELECT StockLocation.StockLocation, [Component Parts].[Component Part No], [Component Parts].[Quantity in Stock], ConcatRelated("[StockLoc]","[QuantityInStock]","[CPID] = " & [Component Parts].[Component Part id]) AS OtherLocation
FROM StockLocation INNER JOIN ([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) ON StockLocation.ID = PartsStockLocation.StockLocationID;
The pair that do not work (and only the where should be different) are
SELECT StockLocation.StockLocationDescription, StockLocation.StockLocation, [Component Parts].[Component Part ID] AS CPID, [Component Parts].[Component Part No], StockLocation_1.StockLocation AS StockLoc, StockLocation.StockLocation AS SL
FROM StockLocation INNER JOIN ((([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) INNER JOIN PartsStockLocation AS PartsStockLocation_1 ON [Component Parts].[Component Part ID] = PartsStockLocation_1.CPartsID) INNER JOIN StockLocation AS StockLocation_1 ON PartsStockLocation_1.StockLocationID = StockLocation_1.ID) ON StockLocation.ID = PartsStockLocation.StockLocationID
WHERE (((StockLocation.StockLocation)=[forms]![Dates for stock reports]![txtStockLoc]));
SELECT StockLocation.StockLocation, [Component Parts].[Component Part No], [Component Parts].[Quantity in Stock], ConcatRelated("[StockLoc]","[QuantityInStock]","[CPID] = " & [Component Parts].[Component Part id]) AS OtherLocation
FROM StockLocation INNER JOIN ([Component Parts] INNER JOIN PartsStockLocation ON [Component Parts].[Component Part ID] = PartsStockLocation.CPartsID) ON StockLocation.ID = PartsStockLocation.StockLocationID;
