Hi 6StringJazzer,

Yes it is a SQL error from ADODB. I tried to run it without the FROM line as you suggested and it gave the same result.

I suspect it is in the first part of the Slect query, but everything i have tried has given the same result.

I have other Sub's running that query a single table and return results without errors. This is my first time Select'ing across several tables.

Here is the full code if you think it will help:

Sub ProductionInv()


 Dim cnnConnect As ADODB.Connection
 Dim rstRecordset As ADODB.Recordset
 Dim Sdate As String
 Dim Edate As String
 Dim rng As Range
 

Set cnnConnect = New ADODB.Connection
cnnConnect.Open _
        "Driver={SQL Server};" & _
        "Server=TRIPSQLTS;" & _
        "Database=xxxxxxxxxx;" & _
        "UID=IWreport;" & _
        "PWD=Ireport;"


Set rng = Worksheets("Report").Range("C3")
Sdate = rng.Value
Edate = rng.Offset(1).Value


Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
    Source:="Select IC_ProductLots.ProductKey, IC_ProductLots.LotKey, IC_ProductLots.ProductionDate, IC_ProductLots.OriginalQuantity_Stk, IC_ProductLots.OriginalQuantity_Alt, IC_Products.ProductCode, IC_Products.Description1, IC_Products.ProductSequence, IC_Products.WarehouseCode, (Select IC_ProductCosts.UnitCost WHERE IC_ProductCosts.ProductCostCode = 3) AS Price, ((Select IC_ProductCosts.UnitCost WHERE IC_ProductCosts.ProductCostCode = 3) * IC_ProductLots.OriginalQuantity_Stk) AS Total" & _
        "Where IC_ProductLots.ProductionDate >= '01/12/2014' AND IC_ProductLots.ProductionDate <='01/13/2014' AND IC_ProductLots.ProductKey = IC_Products.ProductKey AND IC_ProductCosts.ProductCostCode = 3 AND IC_ProductLots.ProductKey = IC_ProductCosts.ProductKey AND IC_ProductLots.ProductKey <> 2088 AND IC_ProductLots.ProductKey <> 741", _
    ActiveConnection:=cnnConnect, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockReadOnly, _
    Options:=adCmdText


With Worksheets("Production.Data").QueryTables.Add( _
        Connection:=rstRecordset, _
        Destination:=Worksheets("Production.Data").Range("A1"))
    .Name = "Inventory"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    .MaintainConnection = False
End With

cnnConnect.Close

Worksheets("Report").Activate

End Sub
Thank you for your help.