I have copied the following SQL statment out of Microsoft Query where
it works correctly and returns the correct data in an Excel worksheet:

SELECT anaylsis.Style, anaylsis.UOM, anaylsis.UnitsBooked,
anaylsis.UnitsReturned, anaylsis.ShippedValue, anaylsis.ReturnsValue,
anaylsis.Territory, anaylsis.ShipYM
FROM ESC.anaylsis anaylsis
WHERE (anaylsis.RecordType='CREDITS') AND
(anaylsis.Territory'20070101' And anaylsis.Territory<'20070801') OR
(anaylsis.RecordType='INVOICES') AND (anaylsis.Territory'20070101'
And anaylsis.Territory<'20070801')

I have the following code:

CurrWkbk = "InventoryTurns.xlsm"

SELECTstmt = "SELECT anaylsis.Style, anaylsis.UnitsShipped,
anaylsis.UnitsReturned, anaylsis.ShippedValue, anaylsis.ReturnsValue,
anaylsis.ShipYM, anaylsis.SalesDate" & "" & Chr(13) & Chr(10)

FROMstmt = "FROM ESC.anaylsis anaylsis" & Chr(13) & "" & Chr(10)

WHEREstmt = "WHERE (anaylsis.SalesDate Between '20070101' And
'20070731') AND (anaylsis.RecordType='CREDITS' Or
anaylsis.RecordType='INVOICES')"

Connection_String = SELECTstmt & FROMstmt & WHEREstmt

Workbooks(CurrWkbk).Worksheets("SalesData").Activa te

With Worksheets("SalesData").QueryTables.Add(Connection := _
"ODBC;DSN=ESC;;DBQ=ESC;CODEPAGE=1252;", _
Destination:=Range("A2"))

.CommandText = Array(Connection_String) ' GIVES TYPE MISMATCH
HERE

.Name = "Sales Data Query From ESC"
.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

End With

Using the immediate window I see that my WHEREstmt is exactly equal to
what I have copied out of Microsoft Query.

Furthermore each of these WHERE statement work in the above code:
WHEREstmt = "WHERE (anaylsis.Territory Between '20070101' And
'20070731')"
WHEREstmt = "WHERE (anaylsis.RecordType='CREDITS' Or
anaylsis.RecordType='INVOICES')"

But for some reason when I combine them with an AND I get a type
mismatch error.

Thanks,

Brent