SQL Query error if too many conditions - Excell 1

[smithcza]

Hi all

We are trying to pull data from a SQL2000 database with Excell. Problem is, as soon as I try to have more than 5 conditions (where...), I get a "Type Mismatch" error. Has anybode got any good suggestions how to work around this?


With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=***;UID=***;PWD=***;APP=Microsoft® Query;WSID=***;DATABASE=***" _
, Destination:=Range(sRange))
.CommandText = Array( _
"SELECT HD_RESULTS.LABCODE, HD_RESULTS.PRO_JOB, HD_RESULTS.SAMPLEIDENT, HD_RESULTS.EXTERNALIDENT, HD_RESULTS.DESCRIPTION, HD_RESULTS.SCH_CODE, HD_RESULTS.ANALYTECODE, HD_RESULTS.SAMPLETYPE, HD_RESULTS.CUID, HD_RESULTS.ANALYTENAME, HD_RESULTS.SCHSEQUEN" _
, _
"CE, HD_RESULTS.ANASEQUENCE, HD_RESULTS.REPDETLIMIT, HD_RESULTS.REPUNITCODE, HD_RESULTS.ROUNDEDVALUE, HD_RESULTS.LIMITREP, HD_RESULTS.USERNOTES, HD_RESULTS.LBATCHCUID, HD_RESULTS.EXPORTED" & Chr(13) & "" & Chr(10) & "FROM CTTI.db" _
, "o.HD_RESULTS HD_RESULTS WHERE (right(HD_RESULTS.PRO_JOB,4) = '" & sValidSample & "') and HD_RESULTS.EXPORTED_MNTH = '0' and (left(HD_RESULTS.SAMPLEIDENT,3) = 'AFT' or left(HD_RESULTS.SAMPLEIDENT,3) = 'AFF') ")
.Name = "Query from BLEH_1"
.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


SmithcZA

 

[xlbo]

Nothing to do with number of where clauses - your SQL text is held in an array. Each element of an array can have a limited no. of characters.

Try this:

 .CommandText = "SELECT HD_RESULTS.LABCODE, HD_RESULTS.PRO_JOB, HD_RESULTS.SAMPLEIDENT, HD_RESULTS.EXTERNALIDENT, HD_RESULTS.DESCRIPTION, HD_RESULTS.SCH_CODE, HD_RESULTS.ANALYTECODE, HD_RESULTS.SAMPLETYPE, HD_RESULTS.CUID, HD_RESULTS.ANALYTENAME, HD_RESULTS.SCHSEQUENCE, HD_RESULTS.ANASEQUENCE, HD_RESULTS.REPDETLIMIT, HD_RESULTS.REPUNITCODE, HD_RESULTS.ROUNDEDVALUE, HD_RESULTS.LIMITREP, HD_RESULTS.USERNOTES, HD_RESULTS.LBATCHCUID, HD_RESULTS.EXPORTED " & _
    "FROM CTTI.dbo.HD_RESULTS HD_RESULTS " & _
    "WHERE (right(HD_RESULTS.PRO_JOB,4) = '" & sValidSample & "') " & _
    "and HD_RESULTS.EXPORTED_MNTH = '0' " & _
    "and (left(HD_RESULTS.SAMPLEIDENT,3) = 'AFT' or left(HD_RESULTS.SAMPLEIDENT,3) = 'AFF') "

For some reason excel automatically populates the SQL text into an array when there is no real need to do so - just remove the brackets and the array call and you should be fine

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smithcza]

Thanks a million, didn't see that. Works like a charm now.

Regards,

SmithcZA