How to solve Complex Query Error !

[arun]

Query is too complex
--------------------------------------------------------------------------------

Hi, I was trying to solve this problem since last two days but couldn't
find any solution.

I wanted to execute a query which is retrieving the records from table1
by
checking the condition for a long long string .
I'm using where clause and checking the condition as-

where (citycode=B and order_no=18900) or (citycode=B and
Order_no=18901).
.......so on upto (citycode=B and order_no=18950)
I'm assigning the string as follows

For i = 18900 To 18950
If i = 18900 Then
ord_string = Trim(ord_string) & "(ctCodOrd = 'B' and ord_no = " & i &
")"
Else
ord_string = Trim(ord_string) & " or (ctCodOrd = 'B' and ord_no = " & i
& ")"
End If
Next i

the query string is as follows-

sqlstr = "select * from track_det2 where " + Trim(ord_string)
Set dy = db.OpenRecordset(sqlstr, dbOpenDynaset)

After executing it is giving the error message " Query is too complex
!" .

I've also tried to solve it by giving the -

db.QueryTimeout = 100 (more than 60)
To give the database enough time to execute this query but it's not
worked.

Plz solve my problem.

amritesh

[RoyVidar]

"arun" <ar***********@gmail.comwrote in message
<11**********************@c28g2000cwb.googlegroups .com>:

Query is too complex
--------------------------------------------------------------------------------

Hi, I was trying to solve this problem since last two days but
couldn't find any solution.

I wanted to execute a query which is retrieving the records from
table1 by
checking the condition for a long long string .
I'm using where clause and checking the condition as-

where (citycode=B and order_no=18900) or (citycode=B and
Order_no=18901).
......so on upto (citycode=B and order_no=18950)
I'm assigning the string as follows

For i = 18900 To 18950
If i = 18900 Then
ord_string = Trim(ord_string) & "(ctCodOrd = 'B' and ord_no = " & i &
")"
Else
ord_string = Trim(ord_string) & " or (ctCodOrd = 'B' and ord_no = " &
i & ")"
End If
Next i

the query string is as follows-

sqlstr = "select * from track_det2 where " + Trim(ord_string)
Set dy = db.OpenRecordset(sqlstr, dbOpenDynaset)

After executing it is giving the error message " Query is too complex
!" .

I've also tried to solve it by giving the -

db.QueryTimeout = 100 (more than 60)
To give the database enough time to execute this query but it's not
worked.

Plz solve my problem.

amritesh

This seems a tad hardcoded, and gives a rather long SQL statement.

Another hardcoded version, could be

ord_string = "ctCodOrd = 'B' and ord_no Between 18900 and 18950"
sqlstr = "select * from track_det2 where " & ord_string

Or if a bit of dynamic is needed

For i = 18900 To 18950
' if some condition?
ord_string = ord_string & "," & cstr(i)
' end if
Next i
If Len(ord_string) Then
ord_string = "ctCodOrd = 'B' AND ord_no IN(" & _
mid$(ord_string, 2) & ")"
Else
ord_string = "ctCodOrd = 'B'"
End If

--
Roy-Vidar