SQL connection string giving type mismatch

[Fid]

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").Activate

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

 

[George Nicholson]

Using the immediate window I see that my WHEREstmt is exactly equal to

what I have copied out of Microsoft Query.

Well, no, it isn't. You've substituted SalesDate for Territory.

WHERE (anaylsis.SalesDate Between '20070101' And '20070731')

Is SalesDate an actual DateTime field? If it is, it requires different
delimiters and a mm/dd/yyyy format regardless of your regional settings:

WHERE (anaylsis.SalesDate Between #01/01/2007# And #07/31/2007#)

HTH,

 

[Vergel Adriano]

Brent,

try it like this:

..CommandText = Connection_String

 

[Fid]

Brent,

try it like this:

.CommandText = Connection_String

Vergel, that solved my problem. Thank you very much.

Brent