why can i use 3 conditions in SQL statement only?

[Guest]

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?

 

[Guest]

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,

 

[Guest]

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 & ")")