Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > SQL connection string giving type mismatch
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 149
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to delete rows giving mismatch error seed Excel Discussion (Misc queries) 8 March 4th 09 10:31 PM
Type Mismatch: array or user defined type expected ExcelMonkey Excel Programming 4 July 6th 06 03:40 PM
Help: Compile error: type mismatch: array or user defined type expected lvcha.gouqizi Excel Programming 1 October 31st 05 08:20 PM
Variant Array with String Values - Type Mismatch jamiee Excel Programming 2 March 7th 04 03:39 AM
Type mismatch? string 2 a long?? CAA[_2_] Excel Programming 4 December 9th 03 02:34 PM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement