Query is too Complex error message when trying to run query.

G

Guest

I'm on a vista computer, using office xp, 2mg of RAM, 2.0ghz dual core
processor.

Would you be able to look at the following query and if possible suggest any
reasons why I am getting an error message "Query too Complex" when trying to
run it. I've tried base-ing the query on a table, and then based it on a
query of that table, including only the fields I wanted. I've shorten the
names of my fields, removed symbols like # that could give me trouble and
still to no avail. I read the article on Microsoft regarding this problem
and thsi is where I thought about these things. Now I have 6 fields that I
am trying to query into from a form, so I don't know if that is too much for
access to handle. I actually started out with 8 fields or 256 lines of
criterias, but brought it down to 6 as I know that this can and has been done
before. Yet I am still not able to run my query. I hope you are able to
help!

Here is my SQL of my query:

PARAMETERS [forms]![reportselection]![from] DateTime,
[forms]![reportselection]![dateto] DateTime,
[forms]![reportselection]![track] Text ( 255 ),
[forms]![reportselection]![client] Text ( 255 ),
[forms]![reportselection]![doctype] Text ( 255 ),
[forms]![reportselection]![po] Text ( 255 ), [forms]![reportselection]![fc]
Value;
SELECT tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref, tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
FROM tbl RIGHT JOIN doc ON tbl.Doc = doc.DocID
GROUP BY tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref, tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
HAVING (((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
..................(((tbl.Rec) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND ((doc.Doc) Like "*" &
[forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Rec) Between
[forms]![reportselection]![from] And [forms]![reportselection]![dateto]) AND
((tbl.Client) Like "*" & [forms]![reportselection]![client] & "*") AND
((doc.Doc) Like "*" & [forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![track]) Is Null))
ORDER BY tbl.Rec, tbl.Dept, tbl.Client, doc.Doc, tbl.FC;


Thanks for your help! It is very much appreciated.
 
G

Gary Walter

Hi Jean,

I believe you are hitting the limit that a HAVING
(or WHERE) clause cannot have more than
40 ANDs.

I might suggest building your SQL in code of
the form with nested IFs (I don't know if I can
even follow your logic to give you a decent example).

It might be something "like"

Dim strSQL As String
Dim strWhere As String
Dim strFrom as String
Dim strDateTo As String
Dim strTrack As String
etc.

strFrom = Trim(Me!from & "")
strDateTo = Trim(Me!dateto & "")
strTrack = Trim(Me!track & "")
etc.

the theory would be that at the end of
each nesting, you would construct your
HAVING clause (strWhere) based on what you have...

IF Len(strFrom) >0 AND IsDate(strFrom)=TRUE THEN
'have [from] date
IF Len(strDateTo) >0 AND IsDate(strDateTo)=TRUE THEN
'have [from] date and [dateto] date
IF Len(strTrack)>0 THEN
'have [from] date and [dateto] date and [track]

'construct strWhere here as example only
'as if this was an end to a nesting
strWhere = "tbl.Track = '" & strTrack & "' AND " _
& "tbl.Rec BETWEEN #" & strFrom & "# AND " _
& "#" & strDateTo & "#"


ELSE
'have [from] date and [dateto] date, don't have [track]


END IF
ELSE
'have [from] date, don't have [dateto] date
IF Len(strTrack)>0 THEN
'have [from] date and [track], don't have [dateto]


ELSE
'have [from] date, don't have [dateto] nor [track]


END IF

END IF

ELSE
'don't have [from] date
IF Len(strDateTo) >0 AND IsDate(strDateTo)=TRUE THEN
'don't have [from] date, have [dateto] date
IF Len(strTrack)>0 THEN
'have [dateto] date and [track], don't have [from]


ELSE
'have [dateto], don't have [from] nor [track]


END IF

ELSE
'don't have [from] nor [dateto]
IF Len(strTrack)>0 THEN
'have [track], don't have [from] nor [dateto]


ELSE
'don't have [from] nor [dateto] nor [track]


END IF

END IF

END IF

'so you come out of nesting with "HAVING" string -- strWhere
strSQL = "SELECT tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, " _
& "tbl.Ref, tbl.PO, tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com " _
& "FROM tbl RIGHT JOIN doc ON tbl.Doc = doc.DocID " _
& "GROUP BY tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, " _
& "tbl.Ref, tbl.PO, tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com " _
& "HAVING " & strWhere

'redefine a stored query (say "qryReport")
CurrentDB.QueryDefs("qryReport").SQL = strSQL
'open report (or form) based on "qryReport"

good luck,

gary

Jean-Francois Gauthier said:
I'm on a vista computer, using office xp, 2mg of RAM, 2.0ghz dual core
processor.

Would you be able to look at the following query and if possible suggest
any
reasons why I am getting an error message "Query too Complex" when trying
to
run it. I've tried base-ing the query on a table, and then based it on
a
query of that table, including only the fields I wanted. I've shorten the
names of my fields, removed symbols like # that could give me trouble and
still to no avail. I read the article on Microsoft regarding this
problem
and thsi is where I thought about these things. Now I have 6 fields that
I
am trying to query into from a form, so I don't know if that is too much
for
access to handle. I actually started out with 8 fields or 256 lines of
criterias, but brought it down to 6 as I know that this can and has been
done
before. Yet I am still not able to run my query. I hope you are able to
help!

Here is my SQL of my query:

PARAMETERS [forms]![reportselection]![from] DateTime,
[forms]![reportselection]![dateto] DateTime,
[forms]![reportselection]![track] Text ( 255 ),
[forms]![reportselection]![client] Text ( 255 ),
[forms]![reportselection]![doctype] Text ( 255 ),
[forms]![reportselection]![po] Text ( 255 ),
[forms]![reportselection]![fc]
Value;
SELECT tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref, tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
FROM tbl RIGHT JOIN doc ON tbl.Doc = doc.DocID
GROUP BY tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref,
tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
HAVING (((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
.................(((tbl.Rec) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND ((doc.Doc) Like "*" &
[forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Rec) Between
[forms]![reportselection]![from] And [forms]![reportselection]![dateto])
AND
((tbl.Client) Like "*" & [forms]![reportselection]![client] & "*") AND
((doc.Doc) Like "*" & [forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![track]) Is Null))
ORDER BY tbl.Rec, tbl.Dept, tbl.Client, doc.Doc, tbl.FC;


Thanks for your help! It is very much appreciated.
Click to expand...
 
D

Douglas J. Steele

In addition to what Gary's suggested, Value isn't a valid data type in the
PARAMETERS declaration statement.

I believe you should be able to simplify your WHERE clause significantly,
though.

It should be possible to do things like:

WHERE (Field1 = [forms]![reportselection]![Control1] OR
[forms]![reportselection]![Control1] IS NULL)
AND (Field2 = [forms]![reportselection]![Control2] OR
[forms]![reportselection]![Control2] IS NULL)
AND (Field3 LIKE "*" & [forms]![reportselection]![Control3] & "*" OR
[forms]![reportselection]![Control3] IS NULL)

rather than the approach you're using. You might also consider Nz in some
cases.

tbl.Rec Between Nz([forms]![reportselection]![from], #1/1/100#) And
Nz([forms]![reportselection]![dateto], #12/31/9999#)
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jean-Francois Gauthier said:
I'm on a vista computer, using office xp, 2mg of RAM, 2.0ghz dual core
processor.

Would you be able to look at the following query and if possible suggest
any
reasons why I am getting an error message "Query too Complex" when trying
to
run it. I've tried base-ing the query on a table, and then based it on
a
query of that table, including only the fields I wanted. I've shorten the
names of my fields, removed symbols like # that could give me trouble and
still to no avail. I read the article on Microsoft regarding this
problem
and thsi is where I thought about these things. Now I have 6 fields that
I
am trying to query into from a form, so I don't know if that is too much
for
access to handle. I actually started out with 8 fields or 256 lines of
criterias, but brought it down to 6 as I know that this can and has been
done
before. Yet I am still not able to run my query. I hope you are able to
help!

Here is my SQL of my query:

PARAMETERS [forms]![reportselection]![from] DateTime,
[forms]![reportselection]![dateto] DateTime,
[forms]![reportselection]![track] Text ( 255 ),
[forms]![reportselection]![client] Text ( 255 ),
[forms]![reportselection]![doctype] Text ( 255 ),
[forms]![reportselection]![po] Text ( 255 ),
[forms]![reportselection]![fc]
Value;
SELECT tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref, tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
FROM tbl RIGHT JOIN doc ON tbl.Doc = doc.DocID
GROUP BY tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref,
tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
HAVING (((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
.................(((tbl.Rec) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND ((doc.Doc) Like "*" &
[forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Rec) Between
[forms]![reportselection]![from] And [forms]![reportselection]![dateto])
AND
((tbl.Client) Like "*" & [forms]![reportselection]![client] & "*") AND
((doc.Doc) Like "*" & [forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![track]) Is Null))
ORDER BY tbl.Rec, tbl.Dept, tbl.Client, doc.Doc, tbl.FC;


Thanks for your help! It is very much appreciated.
Click to expand...
 
G

Gary Walter

Hi Doug,

What you say is all wise and true.

old quote (paraphrased) from USA impeachment process:

"thanks to the impeachment charges,
my mind has concentrated wonderfully."

I have found that in situations
such as Jean-Francois's case,
there is nothing like building
a nested IF/THEN/ELSE ...
to "concentrate the mind wonderfully"... :cool:

even if it means one can later reduce
the logic with confidence...

For me it is like my old circuits class
where the problem always expanded
before it could be reduced to its simplest
form...always...

Of course, with experience (like you have),
the long path is often times not needed...

Maybe its a valid point, I don't know...
maybe I should just go back to bed... :cool:

gary

"Douglas J. Steele" <wrote:
In addition to what Gary's suggested, Value isn't a valid data type in the
PARAMETERS declaration statement.

I believe you should be able to simplify your WHERE clause significantly,
though.

It should be possible to do things like:

WHERE (Field1 = [forms]![reportselection]![Control1] OR
[forms]![reportselection]![Control1] IS NULL)
AND (Field2 = [forms]![reportselection]![Control2] OR
[forms]![reportselection]![Control2] IS NULL)
AND (Field3 LIKE "*" & [forms]![reportselection]![Control3] & "*" OR
[forms]![reportselection]![Control3] IS NULL)

rather than the approach you're using. You might also consider Nz in some
cases.

tbl.Rec Between Nz([forms]![reportselection]![from], #1/1/100#) And
Nz([forms]![reportselection]![dateto], #12/31/9999#)
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Jean-Francois Gauthier" <[email protected]>
wrote in message
I'm on a vista computer, using office xp, 2mg of RAM, 2.0ghz dual core
processor.

Would you be able to look at the following query and if possible suggest
any
reasons why I am getting an error message "Query too Complex" when trying
to
run it. I've tried base-ing the query on a table, and then based it on
a
query of that table, including only the fields I wanted. I've shorten
the
names of my fields, removed symbols like # that could give me trouble and
still to no avail. I read the article on Microsoft regarding this
problem
and thsi is where I thought about these things. Now I have 6 fields
that I
am trying to query into from a form, so I don't know if that is too much
for
access to handle. I actually started out with 8 fields or 256 lines of
criterias, but brought it down to 6 as I know that this can and has been
done
before. Yet I am still not able to run my query. I hope you are able to
help!

Here is my SQL of my query:

PARAMETERS [forms]![reportselection]![from] DateTime,
[forms]![reportselection]![dateto] DateTime,
[forms]![reportselection]![track] Text ( 255 ),
[forms]![reportselection]![client] Text ( 255 ),
[forms]![reportselection]![doctype] Text ( 255 ),
[forms]![reportselection]![po] Text ( 255 ),
[forms]![reportselection]![fc]
Value;
SELECT tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref,
tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
FROM tbl RIGHT JOIN doc ON tbl.Doc = doc.DocID
GROUP BY tbl.Rec, tbl.Dept, tbl.Track, tbl.Client, doc.Doc, tbl.Ref,
tbl.PO,
tbl.FC, tbl.Bun, tbl.Pro, tbl.Paid, tbl.Com
HAVING (((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
((([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![client]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null)) OR
(((tbl.Track)=[forms]![reportselection]![track]) AND ((tbl.Client) Like
"*" &
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null)) OR (((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![po]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Client) Like "*"
&
[forms]![reportselection]![client] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR
.................(((tbl.Rec) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND ((tbl.Client) Like "*" &
[forms]![reportselection]![client] & "*") AND ((doc.Doc) Like "*" &
[forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
(([forms]![reportselection]![fc]) Is Null) AND
(([forms]![reportselection]![track]) Is Null)) OR (((tbl.Rec) Between
[forms]![reportselection]![from] And [forms]![reportselection]![dateto])
AND
((tbl.Client) Like "*" & [forms]![reportselection]![client] & "*") AND
((doc.Doc) Like "*" & [forms]![reportselection]![doctype] & "*") AND
((tbl.PO)=[forms]![reportselection]![po]) AND
((tbl.FC)=[forms]![reportselection]![fc]) AND
(([forms]![reportselection]![track]) Is Null))
ORDER BY tbl.Rec, tbl.Dept, tbl.Client, doc.Doc, tbl.FC;


Thanks for your help! It is very much appreciated.
Click to expand...
Click to expand...
 

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

Need help with Query parameters by form 17
parameters not flowing properly to report 3
Access Determine what query to run based on a checkbox 0
Multiple Parameter Crosstab query filter by form 1
Union Query Edit 1
Read Only Query, need a little help 1
Flexible query 1
yes/no or both Error 3071 2

Top