Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why can i use 3 conditions in SQL statement only?
hi all,
I would like to ask about SQL statements in marco, I used ODBC to connect with AS400, then get my requested data. But when I typed more than 3 conditions, then execute ... it occurred error message. Example 1: select ... from ... where (A = B) and (B = C) and (C = D) it's ok ! Example 2: select ... from ...where (A = B) and (B = C) and (C = D) and (D = E) Error occured ! why? why? ----------------------- Regards, Tarek ^^' ----------------------- Tarek's WorkShop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why can i use 3 conditions in SQL statement only?
Hi,
A few questions: What is the error being returned (number and description)? Could there be a string length limitation in the ODBC driver for AS400 eg 256 or 512 chars? Could you please post the content of the variable containing the sql string (for both 3 & 4 criteria) right before the query. Thanks, -- Regards, Sébastien <http://www.ondemandanalysis.com "Tarek" wrote: hi all, I would like to ask about SQL statements in marco, I used ODBC to connect with AS400, then get my requested data. But when I typed more than 3 conditions, then execute ... it occurred error message. Example 1: select ... from ... where (A = B) and (B = C) and (C = D) it's ok ! Example 2: select ... from ...where (A = B) and (B = C) and (C = D) and (D = E) Error occured ! why? why? ----------------------- Regards, Tarek ^^' ----------------------- Tarek's WorkShop |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why can i use 3 conditions in SQL statement only?
hi, sebastien,
Firstly, thank you for your kindly help ! The following coding is my part of the whole coding. Secondly, according to my excel is chinese version, so the error is about (translated from chinese) : Error code = 13, "Data Type is not match !". And in AS400 side, I know the string type is not over 256 chars is ok, my SQL statement is about 95 - 110 chars, so it should not be overflow ..... i think .... Dim strSQL As String If Range("C6") < Empty Then strSQL = "FA000.SIZCA0 = '" & Range("C6") & "'" End If If Range("E6") < Empty Then If Range("C6") < Empty Then strSQL = strSQL & " AND FA000.CHNCA0 = '" & Range("E6") & "'" Else strSQL = "FA000.CHNCA0 = '" & Range("E6") & "'" End If End If If Range("G6") < Empty Then If Range("C6") < Empty Or Range("E6") < Empty Then strSQL = strSQL & " AND FA000.CLSCA0 = '" & Range("G6") & "'" Else strSQL = "FA000.CLSCA0 = '" & Range("G6") & "'" End If End If If Range("C8") < Empty Then If Range("C6") < Empty Or Range("E6") < Empty Or Range("G6") < Empty Then strSQL = strSQL & " AND FA000.SLDCA0 = '" & Range("C8") & "'" Else strSQL = "FA000.SLDCA0 = '" & Range("C8") & "'" End If End If If Range("E8") < Empty Then If Range("C6") < Empty Or Range("E6") < Empty Or Range("G6") < Empty Or Range("C8") < Empty Then strSQL = strSQL & " AND FA000.SFNCA0 = '" & Range("E8") & "'" Else strSQL = "FA000.SFNCA0 = '" & Range("E8") & "'" End If End If If Range("G8") < Empty Then If Range("C6") < Empty Or Range("E6") < Empty Or Range("G6") < Empty Or Range("C8") < Empty Or Range("E8") < Empty Then strSQL = strSQL & " AND FA000.TAPCA0 = '" & Range("G8") & "'" Else strSQL = "FA000.TAPCA0 = '" & Range("G8") & "'" End If End If With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= WAVEDLIB;", _ Destination:=Range("A14")) .CommandText = Array( _ "SELECT FA000.ITMCA0, FA000.SIZCA0, FA000.CHNCA0, FA000.CLSCA0, FA000.SLDCA0, FA000.SFNCA0, FA000.SL2CA0, FA000.SE2CA0, FA000.TAPCA0" & Chr(13) & "" & Chr(10) & "FROM WAVEDLIB.FA000 FA000" & Chr(13) & "" & Chr(10) & _ "WHERE (" & strSQL & ")") -- Regards, Tarek ^^' ----------------------- Tarek's WorkShop "sebastienm" wrote: Hi, A few questions: What is the error being returned (number and description)? Could there be a string length limitation in the ODBC driver for AS400 eg 256 or 512 chars? Could you please post the content of the variable containing the sql string (for both 3 & 4 criteria) right before the query. Thanks, -- Regards, Sébastien <http://www.ondemandanalysis.com "Tarek" wrote: hi all, I would like to ask about SQL statements in marco, I used ODBC to connect with AS400, then get my requested data. But when I typed more than 3 conditions, then execute ... it occurred error message. Example 1: select ... from ... where (A = B) and (B = C) and (C = D) it's ok ! Example 2: select ... from ...where (A = B) and (B = C) and (C = D) and (D = E) Error occured ! why? why? ----------------------- Regards, Tarek ^^' ----------------------- Tarek's WorkShop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement On Two Conditions | Excel Worksheet Functions | |||
Conditions with IF statement | Excel Discussion (Misc queries) | |||
IF Statement with multiple conditions | Excel Discussion (Misc queries) | |||
Nesting If Statement with "AND", "OR" conditions | Excel Worksheet Functions | |||
3 conditions if else statement. | Excel Programming |