ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   SQL connection string giving type mismatch (https://www.excelbanter.com/excel-programming/396110-sql-connection-string-giving-type-mismatch.html)

Fid[_2_]
SQL connection string giving type mismatch
 
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

George Nicholson
SQL connection string giving type mismatch
 
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,


"Fid" wrote in message
oups.com...
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

Vergel Adriano
SQL connection string giving type mismatch
 
Brent,

try it like this:

..CommandText = Connection_String


--
Hope that helps.

Vergel Adriano


"Fid" wrote:

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

Fid[_2_]
SQL connection string giving type mismatch
 
On Aug 22, 1:34 pm, Vergel Adriano
wrote:
Brent,

try it like this:

.CommandText = Connection_String

--
Hope that helps.

Vergel Adriano

"Fid" wrote:
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

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

Brent


All times are GMT +1. The time now is 12:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com