My query does run, but returns no results:
SET NoCount ON
SELECT
Inventory.EffectiveDate,
Inventory.Quantity,
Inventory.SourceType,
Inventory.PickingLocation,
Inventory.SourceInventory,
Locations.LocationId,
Customers.CustomerName,
Products.ProductId,
LocationFrom.LocationId as lFrom,
LocationTo.LocationId as lTo
FROM (((((((dbo.Inventory AS Inventory
LEFT JOIN dbo.Products AS Products ON Products.Product = Inventory.Product )
LEFT JOIN dbo.Locations AS Locations ON Locations.Location = Inventory.Location )
LEFT JOIN dbo.Customers AS Customers ON Customers.ConsignmentLocation = Inventory.Location )
LEFT JOIN dbo.Inventory AS SourceLocation ON SourceLocation.Inventory = Inventory.SourceInventory)
LEFT JOIN dbo.Locations AS LocationFrom ON LocationFrom.Location = SourceLocation.Location )
LEFT JOIN dbo.Inventory AS TargetLocation ON TargetLocation.Inventory = Inventory.TargetInventory)
LEFT JOIN dbo.Locations AS LocationTo ON LocationTo.Location = TargetLocation.Location)
WHERE
(Inventory.SourceType = 'Q' OR Inventory.SourceType = 'G' OR Inventory.SourceType = 'P' OR Inventory.SourceType = 'A' OR Inventory.SourceType = 'B')
AND
((Inventory.EffectiveDate >= 2011-12-30 And Inventory.EffectiveDate <= 2011-12-31));
This query runs from Excel fine. But I was looking for the tool to be able to see tables, that's why I'm using Access - but it gives me more problems....
You need to surround your date parameters with single quotes.
Inventory.EffectiveDate >= '2011-12-30'
You should also consider using shorter aliases to make the code more concise. I'm not sure of the purpose of using an alias like Products to represent dbo.Products... you should also eliminate all the unnecessary parentheses if Access doesn't force them on you.
SET NOCOUNT ON;
SELECT
inv.EffectiveDate,
inv.Quantity,
inv.SourceType,
inv.PickingLocation,
inv.SourceInventory,
loc.LocationId,
cust.CustomerName,
prod.ProductId,
lFrom.LocationId as lFrom,
lTo.LocationId as lTo
FROM dbo.Inventory AS inv
LEFT OUTER JOIN dbo.Products AS prod ON prod.Product = inv.Product
LEFT OUTER JOIN dbo.Locations AS loc ON loc.Location = inv.Location
LEFT OUTER JOIN dbo.Customers AS cust ON inv.Location = cust.ConsignmentLocation
LEFT OUTER JOIN dbo.Inventory AS src ON src.Inventory = inv.SourceInventory
LEFT OUTER JOIN dbo.Locations AS lFrom ON lFrom.Location = src.Location
LEFT OUTER JOIN dbo.Inventory AS trg ON trg.Inventory = inv.TargetInventory
LEFT OUTER JOIN dbo.Locations AS lTo ON lTo.Location = trg.Location
WHERE
inv.SourceType IN ('Q', 'G', 'P', 'A', 'B')
AND inv.EffectiveDate >= '2011-12-30'
AND inv.EffectiveDate <= '2011-12-31'; -- suspect you want < '2012-01-01' here
-- unless your column doesn't store time.