Need some help simplifying my query

[SteelFire via AccessMonster.com]

Every time I try to run my query, telling me that "This expression is typed
incorrectly, or it is too complex to be evaluated." I have checked to the
best of what I know and it all looks right, so I am going with the last part
if the error. The SQL of my query is:

SELECT tblPartTable.ID, tblPartTable.ProjectName, tblPartTable.
PartDescription, tblPartTable.PartNum, tblPartTable.JobNum, tblPartTable.
CategoryName, tblPartTable.CategoryType, tblPartTable.Rev, tblPartTable.
RevDate, tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.
PartDrawingAtt, tblPartTable.Notes

FROM tblPartTable

WHERE (((tblPartTable.CategoryName )=[Forms]![frmPartFinder]![cboCategory])
OR (((tblPartTable.CategoryName ) Like [Forms]![frmPartFinder]![cboCategory])
Is Null))

AND (((tblPartTable.CategoryType )=[Forms]![frmPartFinder]![cboType]) OR ((
(tblPartTable.CategoryType ) Like [Forms]![frmPartFinder]![cboType]) Is Null))


AND (((tblPartTable.RevDate )>=[Forms]![frmPartFinder]![txtSDate]) OR ((
(tblPartTable.RevDate ) Like [Forms]![frmPartFinder]![txtSDate]) Is Null))

AND (((tblPartTable.RevDate )<[Forms]![frmPartFinder]![txtEDate]) OR ((
(tblPartTable.RevDate ) Like [Forms]![frmPartFinder]![txtEDate]) Is Null))

AND (((tblPartTable.PartNum)=[Forms]![frmPartFinder]![cboPart]) OR ((
(tblPartTable.PartNum) Like [Forms]![frmPartFinder]![cboPart]) Is Null))

AND (((tblPartTable.JobNum )=[Forms]![frmPartFinder]![cboJob]) OR ((
(tblPartTable.JobNum ) Like [Forms]![frmPartFinder]![cboJob]) Is Null))

AND (((tblPartTable.PartDescription ) Like ""*" & [Forms]![frmPartFinder]!
[txtDesc] & "*"") OR (((tblPartTable.PartDescription ) Like [Forms]!
[frmPartFinder]![cboPart]) Is Null));

The empty lines are just to make it easier to read. If you know of any way to
get the same WHERE out put in a different way, then that would help. Also if
you spot something is "typed incorrectly", could you also point that out. I
have been working on this all day and my head is going to pop soon.

Any help is welcome.

 

[John Spencer]

Try the following. Note the construction of each set of criteria. With
this many criteria set up as below, you can still get a query too
complex error.

SELECT tblPartTable.ID, tblPartTable.ProjectName
, tblPartTable.PartDescription, tblPartTable.PartNum
, tblPartTable.JobNum, tblPartTable.CategoryName
, tblPartTable.CategoryType, tblPartTable.Rev
, tblPartTable.RevDate, tblPartTable.CustomerID
, tblPartTable.FilePath, tblPartTable.PartDrawingAtt
, tblPartTable.Notes

FROM tblPartTable

WHERE (tblPartTable.CategoryName =[Forms]![frmPartFinder]![cboCategory]
OR [Forms]![frmPartFinder]![cboCategory] Is Null))

AND (tblPartTable.CategoryType =[Forms]![frmPartFinder]![cboType]
OR [Forms]![frmPartFinder]![cboType] Is Null)

AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate]
OR [Forms]![frmPartFinder]![txtSDate] Is Null)

AND (tblPartTable.RevDate <[Forms]![frmPartFinder]![txtEDate]
OR [Forms]![frmPartFinder]![txtEDate] Is Null)

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart]
OR [Forms]![frmPartFinder]![cboPart] Is Null)

AND (tblPartTable.JobNum =[Forms]![frmPartFinder]![cboJob]
OR [Forms]![frmPartFinder]![cboJob] Is Null)

AND (tblPartTable.PartDescription
Like "*" & [Forms]![frmPartFinder]![txtDesc] & "*"
OR [Forms]![frmPartFinder]![cboPart] Is Null)

One possibility if some of the fields are REQUIRED (always have a value)
is to change some of the criteria to make the query simpler. For
instance, if a PartNum is ALWAYS filled in and is a text field you can
use the following

AND tblPartTable.PartNum LIKE Nz([Forms]![frmPartFinder]![cboPart],"*"

to replace

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart]
OR [Forms]![frmPartFinder]![cboPart] Is Null)

Likewise with text description you could use this if there is always a
value in PartDescription.

AND tblPartTable.PartDescription
Like "*" & [Forms]![frmPartFinder]![txtDesc] & "*"

RevDate would be trickier but again if it always had a value you could
replace
AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate]
OR [Forms]![frmPartFinder]![txtSDate] Is Null)

With
AND tblPartTable.RevDate >=
Nz([Forms]![frmPartFinder]![txtSDate],#1/1/1900#)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[SteelFire via AccessMonster.com]

I got to work today dreading that fact that I had a lot to debug and work out,
but then I found that you had not only given an answer, but now I know that
the longer way that I had been doing things could be made a lot shorter. I
made the changes and every thing was working just as it should. Thank you for
making my work load that much lighter.

Try the following. Note the construction of each set of criteria. With
this many criteria set up as below, you can still get a query too
complex error.

SELECT tblPartTable.ID, tblPartTable.ProjectName
, tblPartTable.PartDescription, tblPartTable.PartNum
, tblPartTable.JobNum, tblPartTable.CategoryName
, tblPartTable.CategoryType, tblPartTable.Rev
, tblPartTable.RevDate, tblPartTable.CustomerID
, tblPartTable.FilePath, tblPartTable.PartDrawingAtt
, tblPartTable.Notes

FROM tblPartTable

WHERE (tblPartTable.CategoryName =[Forms]![frmPartFinder]![cboCategory]
OR [Forms]![frmPartFinder]![cboCategory] Is Null))

AND (tblPartTable.CategoryType =[Forms]![frmPartFinder]![cboType]
OR [Forms]![frmPartFinder]![cboType] Is Null)

AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate]
OR [Forms]![frmPartFinder]![txtSDate] Is Null)

AND (tblPartTable.RevDate <[Forms]![frmPartFinder]![txtEDate]
OR [Forms]![frmPartFinder]![txtEDate] Is Null)

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart]
OR [Forms]![frmPartFinder]![cboPart] Is Null)

AND (tblPartTable.JobNum =[Forms]![frmPartFinder]![cboJob]
OR [Forms]![frmPartFinder]![cboJob] Is Null)

AND (tblPartTable.PartDescription
Like "*" & [Forms]![frmPartFinder]![txtDesc] & "*"
OR [Forms]![frmPartFinder]![cboPart] Is Null)

One possibility if some of the fields are REQUIRED (always have a value)
is to change some of the criteria to make the query simpler. For
instance, if a PartNum is ALWAYS filled in and is a text field you can
use the following

AND tblPartTable.PartNum LIKE Nz([Forms]![frmPartFinder]![cboPart],"*"

to replace

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart]
OR [Forms]![frmPartFinder]![cboPart] Is Null)

Likewise with text description you could use this if there is always a
value in PartDescription.

AND tblPartTable.PartDescription
Like "*" & [Forms]![frmPartFinder]![txtDesc] & "*"

RevDate would be trickier but again if it always had a value you could
replace
AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate]
OR [Forms]![frmPartFinder]![txtSDate] Is Null)

With
AND tblPartTable.RevDate >=
Nz([Forms]![frmPartFinder]![txtSDate],#1/1/1900#)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================