Access Report throwing error

[kermitforney]

I have an Access report that is pulling data from a SQL Server 2008 backend. For some reason when I use a derived table. Not sure why this is occurring, but any help would be appreciated. The derived table in question is highlighted in [red]red[/red].

Error Message - Invalid SQL Statement. Check the server filter on the form record source

TSQL Sample

SELECT	vwInvOHbyCategorySupply.strWarehouse
		,vwInvOHbyCategorySupply.strItemGroupName
		,ISNULL(s.pieces_ordered, 0) AS pieces_ordered
		,ISNULL(s.pieces_filled, 0) AS pieces_filled
		,ISNULL(s.Numbers_orders, 0) AS Numbers_orders
		,ISNULL(s.whls_value, 0) AS whls_value
		,ISNULL(s.cost_value, 0) AS cost_value
		,vwInvOHbyCategorySupply.Inv_OH
		,s.Gt_ordered, s.Gt_filled
		,CASE vwInvOHbyCategorySupply.strItemGroupName 
			WHEN 'SUPPLIES' 
			THEN t .TotOrders 
			ELSE 0 
		END AS TotOrders
		
		
FROM    [red](SELECT	[strWarehouse]
				,[strItemGroupName]
				,[Inv_OH]
		
		FROM	vwInvOHbyCategorySupply
		
		WHERE	vwInvOHbyCategorySupply.strItemGroupName IN ('CUST REQUEST', 'IN LINE', 'SUPPLIES MISC.', 'SUPPLIES RSO', 'SUPPLIES WHSE')) vwInvOHbyCategorySupply [/red]
		
	LEFT OUTER JOIN
					(SELECT	vwSalesbyCategorySupply.strWareHouse
							,vwSalesbyCategorySupply.strItemGroupName
							,SUM(vwSalesbyCategorySupply.Pieces_Ordered) AS pieces_ordered
							,SUM(vwSalesbyCategorySupply.Pieces_Filled) AS pieces_filled
							,SUM(vwSalesbyCategorySupply.Num_orders) AS Numbers_orders
							,SUM(vwSalesbyCategorySupply.whls_value) AS whls_value
							,SUM(vwSalesbyCategorySupply.cost_value) AS cost_value
							,SUM(Goods_ordered) AS Gt_ordered
							,SUM(Goods_filled) AS Gt_filled
							
					FROM	vwSalesbyCategorySupply
					
                    WHERE	vwSalesbyCategorySupply.invoice_date >= '8/1/2007' 
								AND vwSalesbyCategorySupply.invoice_date <= '8/1/2007 23:59:59'
                    
                    GROUP BY	vwSalesbyCategorySupply.strWareHouse
								,vwSalesbyCategorySupply.strItemGroupName) s 
								
		ON	 s.strWareHouse = vwInvOHbyCategorySupply.strWarehouse 
			AND s.strItemGroupName = vwInvOHbyCategorySupply.strItemGroupName 
	INNER JOIN
			       (SELECT	strWareHouse
							,COUNT(DISTINCT lngOrderID) TotOrders
                    
                    FROM	vwTotalNumberofOrders
                            
                    WHERE   strItemGroupName IN ('SUPPLIES', 'IN LINE', 'CUST REQUEST') 
						AND datValidateFinish BETWEEN '8/1/2007' AND '8/1/2007 23:59:59'
                            
                    GROUP BY strWarehouse) t 
		
		ON t .strWareHouse = vwInvOHbyCategorySupply.strWarehouse

 

[lameid]

Is this SQL in a Passthrough query or used in code to directly connect to SQL Server?

If so, nothing jumps out at me, otherwise, Jet SQL only takes the hash (#) as a date/time delimeter. I have also had problems with JET playing nice with sub-queries as literal SQL. The latter is hit and miss. Just save each select statment as a query object and use the queryname.

If it is going straight to SQL, can you get the SQL to run in SQL Server directly?