Simple select query not retrieving correct results

[snypa]

Hi guys

why would a query with multiple criterias pull together with an OR operator failed to return any results which meet any of the creteria conditions>

SELECT First(tblProjectDetails.ProjectDetailId) AS FirstOfProjectDetailId, tblProjectDetails.CommittedCosts, tblProjectDetails.PackageNumber, tblProjectDetails.ItemNumber, qryMatchingtblPOCommittmentbtlProjectDetails.PoAmount, [tblProjectDetails].[CommittedCosts]+[poAmount] AS TotalCost, qryMatchingtblPOCommittmentbtlProjectDetails.itemDescription, qryMatchingtblPOCommittmentbtlProjectDetails.PoQty INTO qrytblProjectDetailsUpdateRowsFrmTblPOCommittment
FROM qryMatchingtblPOCommittmentbtlProjectDetails INNER JOIN tblProjectDetails ON (qryMatchingtblPOCommittmentbtlProjectDetails.MaxOfCommittedCosts = tblProjectDetails.CommittedCosts) AND (qryMatchingtblPOCommittmentbtlProjectDetails.ItemNumber = tblProjectDetails.ItemNumber) AND (qryMatchingtblPOCommittmentbtlProjectDetails.PackageNumber = tblProjectDetails.PackageNumber)
GROUP BY tblProjectDetails.CommittedCosts, tblProjectDetails.PackageNumber, tblProjectDetails.ItemNumber, qryMatchingtblPOCommittmentbtlProjectDetails.PoAmount, [tblProjectDetails].[CommittedCosts]+[poAmount], qryMatchingtblPOCommittmentbtlProjectDetails.itemDescription, qryMatchingtblPOCommittmentbtlProjectDetails.PoQty, qryMatchingtblPOCommittmentbtlProjectDetails.cmsFacility, qryMatchingtblPOCommittmentbtlProjectDetails.cmsCommodity, qryMatchingtblPOCommittmentbtlProjectDetails.carNumber
HAVING (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsFacility) Is Null)) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsCommodity) Is Null)) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.carNumber) Is Null)) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsFacility) Like "N*")) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsCommodity) Like "N*")) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.carNumber) Like "N*")) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsFacility)="")) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsCommodity)="")) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.carNumber)=""));

There are some which meet

(((qryMatchingtblPOCommittmentbtlProjectDetails.cmsCommodity) Is Null)) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.carNumber) Is Null)) OR (((qryMatchingtblPOCommittmentbtlProjectDetails.cmsFacility) Like "N*"))

but they are not dispalyed, why would this be the case??

 

[Uncle Gizmo]

>>>Simple select query not retrieving correct results<<<<

It's not a simple select query, it's got a GROUP BY and a HAVING clause as well....

 

[snypa]

Yes but will that affect the results or the sorting?

 

[Uncle Gizmo]

I would suggest you create a set of sample data gradually adding to it the various criteria until you see where it breaks.

 

[wiklendt]

Uncle Gizmo is right - it's not a simple query. Instead of the SQL, can you post the Design view of this query? have you made sure your join properties are correct?