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

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

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