3035 Exceeded System Resources - Query Too Complex

B

BlueWolverine

Hello,
MS Access 2003 on XP PRO (4GB RAM).

SELECT q_lvl6a_OIS.ID, q_lvl6a_OIS.OISAVAIL
FROM q_lvl6a_OIS RIGHT JOIN q_lvl5_WED ON q_lvl6a_OIS.ID = q_lvl5_WED.ID
GROUP BY q_lvl6a_OIS.ID, q_lvl6a_OIS.OISAVAIL
ORDER BY q_lvl6a_OIS.ID;

Access thinks this is too complex. I recently added fields to my admittedly
bloated table (Around 40-50 fields) but they never get mentioned in the query
string. And that table is way at lvl2-3.

This worked before I made those changes and now, for some reason, it's
broken. I also tore 5 unnecessary fields out of the table just lighten up
the load.

I have backed up and Compact/Repaired already. didn't help.

This is very irritating. Any ideas? I can send every invovle query if need
be but this is the only one that dies. (along with lvlx after it)
 
M

Michel Walsh

Jet is limited to 2Gig, and another 2Gig for temporary storage. It seems
your temporary storage hit the limit, mainly if q_lvl_XYZ are queries
themselves, their result is maintained, in full, in the temporary storage
space, and so is the result of other queries those queries may have called,
and so on, in cascade.

Revise the logic of your solution, if possible. Has very little to do with
the top query you presented, I am afraid.


Vanderghast, Access MVP
 
B

BlueWolverine

Why did it suddenly die? It worked before.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Michel Walsh said:
Jet is limited to 2Gig, and another 2Gig for temporary storage. It seems
your temporary storage hit the limit, mainly if q_lvl_XYZ are queries
themselves, their result is maintained, in full, in the temporary storage
space, and so is the result of other queries those queries may have called,
and so on, in cascade.

Revise the logic of your solution, if possible. Has very little to do with
the top query you presented, I am afraid.


Vanderghast, Access MVP
Click to expand...
 
B

BlueWolverine

This SQL use to be a lot simpler. Somehow, access made this way more
complicated than it used to be.

Would this be the crash causer and how can I fix this so that it doesn't
re-write my code?

Thanks/


SELECT t_OIS.ID, First(IIf([Instructions] Is Null,False,True)) AS OISAVAIL
FROM t_OIS
WHERE (((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))=False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))=False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))=False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))=False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))=False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR (((t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*"
& [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND ((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True))
GROUP BY t_OIS.ID
ORDER BY t_OIS.ID;



--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Michel Walsh said:
Jet is limited to 2Gig, and another 2Gig for temporary storage. It seems
your temporary storage hit the limit, mainly if q_lvl_XYZ are queries
themselves, their result is maintained, in full, in the temporary storage
space, and so is the result of other queries those queries may have called,
and so on, in cascade.

Revise the logic of your solution, if possible. Has very little to do with
the top query you presented, I am afraid.


Vanderghast, Access MVP
Click to expand...
 
M

Michel Walsh

The repeated OR is LIKELY the killer. I am surprised you didn't get an error
that the query was too complex. And it is probably slow, at execution time.

I don't think you can do much, without re-writing the query. I suspect, at
first glance, that you could be better by writing a query case by case (ad
hoc) as a string, in VBA code:

strSQL = "SELECT ... "
if (condition) then
strSQL= strSQL & " WHERE this and this and that "
else if (other condition)
strSQL = strSQL & " WHERE that and that and thatToo "
else ..
...
end if

And then, use that string (probably to define a temporary saved query, or
otherwise as it fits). The idea is to make a CLEAN where clause, without
much ORing stuff. You will surely win on execution time, since it will be
better optimized. OR do not only likely kill intermediate storage space, but
also likely kill the optimality of the execution plan.


Vanderghast, Access MVP



BlueWolverine said:
This SQL use to be a lot simpler. Somehow, access made this way more
complicated than it used to be.

Would this be the crash causer and how can I fix this so that it doesn't
re-write my code?

Thanks/


SELECT t_OIS.ID, First(IIf([Instructions] Is Null,False,True)) AS OISAVAIL
FROM t_OIS
WHERE (((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR (((t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True))
GROUP BY t_OIS.ID
ORDER BY t_OIS.ID;
Click to expand...
 
B

BlueWolverine

Like I said, I wrote the query and it was maybe 30 lines total or something,
but then I went into it later and Access had completely changed it.

I guess I'll re-write it, keep a copy in a notepad file, and dump it if it
ever breaks...

I'll check other queries to see if they went ape$#!T as well.

Thanks a ton.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Michel Walsh said:
The repeated OR is LIKELY the killer. I am surprised you didn't get an error
that the query was too complex. And it is probably slow, at execution time.

I don't think you can do much, without re-writing the query. I suspect, at
first glance, that you could be better by writing a query case by case (ad
hoc) as a string, in VBA code:

strSQL = "SELECT ... "
if (condition) then
strSQL= strSQL & " WHERE this and this and that "
else if (other condition)
strSQL = strSQL & " WHERE that and that and thatToo "
else ..
...
end if

And then, use that string (probably to define a temporary saved query, or
otherwise as it fits). The idea is to make a CLEAN where clause, without
much ORing stuff. You will surely win on execution time, since it will be
better optimized. OR do not only likely kill intermediate storage space, but
also likely kill the optimality of the execution plan.


Vanderghast, Access MVP



BlueWolverine said:
This SQL use to be a lot simpler. Somehow, access made this way more
complicated than it used to be.

Would this be the crash causer and how can I fix this so that it doesn't
re-write my code?

Thanks/


SELECT t_OIS.ID, First(IIf([Instructions] Is Null,False,True)) AS OISAVAIL
FROM t_OIS
WHERE (((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))=False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=False)) OR (((t_OIS.Instructions) Like
"*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS5] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like "*" &
[Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS3]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS4] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions) Like
"*"
& [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS2] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS1] & "*" And (t_OIS.Instructions)
Like
"*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
(((IsNull([Forms]![f_SearchPanel]![OIS1]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS2]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS4]))<>False) AND
((IsNull([Forms]![f_SearchPanel]![OIS5]))<>False) AND
((t_OIS.Instructions)
Like "*" & [Forms]![f_SearchPanel]![OIS3] & "*") AND
(([FORMS]![f_SearchPanel]![ANDOR2])=True)) OR
Click to expand...
Click to expand...
 
M

MGFoster

BlueWolverine said:
Like I said, I wrote the query and it was maybe 30 lines total or something,
but then I went into it later and Access had completely changed it.

I guess I'll re-write it, keep a copy in a notepad file, and dump it if it
ever breaks...

I'll check other queries to see if they went ape$#!T as well.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yeah, isn't Access a PITA when it does that. I've found that if you
write it in SQL View then change to Design Grid view Access will re
arrange the SQL. Therefore, I try to never switch to Design Grid view
when I write a complex WHERE clause. Save the Query while in SQL View
to avoid that from happening.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbBttoechKqOuFEgEQIlgQCg1qQVD5pkg20SUsRzc7rKqM10qxoAoI0S
SFcBQPnu4wMEkCrZ4v1bMBfo
=Id5V
-----END PGP SIGNATURE-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time Error 3035, System resource exceeded 4
RUN TIME ERROR 3035 System Resource Exceeded 1
System Resource Exceeded/Query Too Complex 4
too complex query 5
'system resource exceeded' and 'query too complex' 4
<ack!!!> simplify to resolve Query too complex <ack!!!> 3
query too complex when transposing 2
Query is too complex 5

Top