Query criteria not working

[thart21]

I have a query with several criteria

SELECT tblProjectDetails.projectid, SubType.SubType, tblProjectDetails.status, tblProjectDetails.product, tblProjectDetails.description, tblProjectDetails.type, tblProjectDetails.producttype, tblProjectDetails.confidence, tblProjectDetails.flatamount, IIf(IsNull([reduction]),(([currentprice]-[newprice])*[volume])/(13-[cutinmonthnmbr]),(([reduction]*[volume])/(13-[cutinmonthnmbr]))) AS Calc, ([flatamount]/(13-[cutinmonthnmbr])) AS Calc2, IIf(1>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Jan, IIf(2>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Feb, IIf(3>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Mar, IIf(4>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS April, IIf(5>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS May, IIf(6>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS June, IIf(7>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS July, IIf(8>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Aug, IIf(9>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Sept, IIf(10>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Oct, IIf(11>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Nov, IIf(12>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS [Dec], tblProjectDetails.cutinmonth, tblProjectDetails.DMO, tblProjectDetails.fcstloc, tblProjectDetails.projecttype, tblManagers.last, tblManagers.managerid, tblProjectDetails.currentsupplier
FROM tblManagers RIGHT JOIN (SubType RIGHT JOIN tblProjectDetails ON SubType.SubTypeID = tblProjectDetails.subtypeid) ON tblManagers.managerid = tblProjectDetails.managernew
WHERE (((tblProjectDetails.status)<>"Concept" And (tblProjectDetails.status)<>"Tabled") AND ((tblProjectDetails.producttype)<>"3") AND ((tblProjectDetails.confidence)="1") AND ((tblProjectDetails.cutinmonth)<>13) AND ((tblProjectDetails.DMO)<>Yes) AND ((tblProjectDetails.fcstloc) Is Null) AND ((tblProjectDetails.projecttype)<>"Other") AND ((tblProjectDetails.cutinyear)=2006) AND ((tblProjectDetails.currentsupplier)<>"Venture"));

Everything works great except for the very last criteria <> "Venture". I have one project that this query should leave out but it eliminates any project with a Null in the supplier field instead of just supplier "Venture". Why would it do that and what would be the solution?

Thanks

Toni

 

[supercharge]

Is it just me that seeing stars and crawling worms on my monitor? Please...

 

[selenau837]

SELECT tblProjectDetails.projectid, SubType.SubType, tblProjectDetails.status, tblProjectDetails.product, tblProjectDetails.description, tblProjectDetails.type, tblProjectDetails.producttype, tblProjectDetails.confidence, tblProjectDetails.flatamount, IIf(IsNull([reduction]),(([currentprice]-[newprice])*[volume])/(13-[cutinmonthnmbr]),(([reduction]*[volume])/(13-[cutinmonthnmbr]))) AS Calc, ([flatamount]/(13-[cutinmonthnmbr])) AS Calc2, IIf(1>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Jan, IIf(2>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Feb, IIf(3>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Mar, IIf(4>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS April, IIf(5>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS May, IIf(6>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS June, IIf(7>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS July, IIf(8>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Aug, IIf(9>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Sept, IIf(10>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Oct, IIf(11>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Nov, IIf(12>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS [Dec], tblProjectDetails.cutinmonth, tblProjectDetails.DMO, tblProjectDetails.fcstloc, tblProjectDetails.projecttype, tblManagers.last, tblManagers.managerid, tblProjectDetails.currentsupplier
FROM tblManagers RIGHT JOIN (SubType RIGHT JOIN tblProjectDetails ON SubType.SubTypeID = tblProjectDetails.subtypeid) ON tblManagers.managerid = tblProjectDetails.managernew
WHERE (((tblProjectDetails.status)<>"Concept" And (tblProjectDetails.status)<>"Tabled") AND ((tblProjectDetails.producttype)<>"3") AND ((tblProjectDetails.confidence)="1") AND ((tblProjectDetails.cutinmonth)<>13) AND ((tblProjectDetails.DMO)<>Yes) AND ((tblProjectDetails.fcstloc) Is Null) AND ((tblProjectDetails.projecttype)<>"Other") AND ((tblProjectDetails.cutinyear)=2006) AND ((tblProjectDetails.currentsupplier)<>"Venture"));

Here is the code wrapped so it is easier to view.

Just trying to hlep you out so someone can hopefully help.

BTW, have you tried to take out that criteria and see what comes up? Does it pull Venture at that point?