Can't get query to display data with values over 0 only, based on a control value

[achammar]

Hi all, me again learning how to work with queries. I have a query that simply displays certain records depending on criteria that is based on the value of controls on a form. I got it to read check boxes and display only the data of the checkboxes that are checked. It works good except I can't get it to work to list only the values of a field that are greater than 0. Here is my sql... It's the last part that I can't get to work where

SELECT tblSheetPlate.[Part Number], tblSheetPlate.Part_Type, tblSheetPlate.Material, tblSheetPlate.Thickness, tblSheetPlate.Width, tblSheetPlate.Length, tblSheetPlate.Notes, tblSheetPlate.Qnty_On_Hand, tblSheetPlate.Min_Qnty, tblSheetPlate.Max_Qnty, tblSheetPlate.Location, tblSheetPlate.pValue
FROM tblSheetPlate
WHERE (((tblSheetPlate.Part_Type)=IIf([Forms]![frmSheetPlate]![chkPlateSP]=-1,[Forms]![frmSheetPlate]![chkPlateSP].[Tag],"«falsepart»") Or (tblSheetPlate.Part_Type)=IIf([Forms]![frmSheetPlate]![chkSheetSp]=-1,[Forms]![frmSheetPlate]![chkSheetSp].[Tag],"«falsepart»")) AND ((tblSheetPlate.Material)=IIf([Forms]![frmSheetPlate]![chkCarbonMat]=-1,[Forms]![frmSheetPlate]![chkCarbonMat].[Tag],"«falsepart»") Or (tblSheetPlate.Material)=IIf([Forms]![frmSheetPlate]![chkStainlessMat]=-1,[Forms]![frmSheetPlate]![chkStainlessMat].[Tag],"«falsepart»") Or (tblSheetPlate.Material)=IIf([Forms]![frmSheetPlate]![chkAluminumMat]=-1,[Forms]![frmSheetPlate]![chkAluminumMat].[Tag],"«falsepart»")) AND ((tblSheetPlate.Qnty_On_Hand)=IIf([Forms]![frmSheetPlate]![chkOverZero]=-1,[Forms]![frmSheetPlate]![chkOverZero]>0,"«falsepart»")));


It all works except the part after the last AND. It won't show only records with a value over 0. It actually shows only records with a value of 0.. the opposite of what I want it to do.
All the controls are check boxes and when checked their value is -1

This is the part that won't work:
AND ((tblSheetPlate.Qnty_On_Hand)=IIf([Forms]![frmSheetPlate]![chkOverZero]=-1,[Forms]![frmSheetPlate]![chkOverZero]>0,"«falsepart»")));

And I don't know what to put in the place of "falsepart" for the rest of it, but it works just leaving it.. lol

Any ideas?

Thank you!

[CJ_London]

using iif in sql does not need a falsepart (it does in vba, but not sql)

(tblSheetPlate.Part_Type)=IIf([Forms]![frmSheetPlate]![chkPlateSP]=-1,[Forms]![frmSheetPlate]![chkPlateSP].[Tag],"«falsepart»")

however using this as an example you would need to include a falsepart

(tblSheetPlate.Part_Type)=IIf([Forms]![frmSheetPlate]![chkPlateSP]=-1,[Forms]![frmSheetPlate]![chkPlateSP].[Tag],tblSheetPlate.Part_Type)

but I would be more inclined to write it this way

((tblSheetPlate.Part_Type)=[Forms]![frmSheetPlate]![chkPlateSP].[Tag] AND [Forms]![frmSheetPlate]![chkPlateSP]=-1)

with regards your not working - you need to explain what it is supposed to be doing. if you read it as a sentence at the moment it is saying



Qnty_On_Hand=if chkOverzero=true then chkOverZero>0

chkOverZero>0 can only be true or false (0r -1 or 0). I presume Qnty_On_Hand is a number

[achammar]

Thank you again Ajax! Yes, Qnty_On_Hand is a number field in my table. I want that last part to restrict my query to show only the records that have a value greater than 0 in that field if the chkOverZero checkbox is checked, so if the value is zero for that field, then I don't want those records to show, but only if that checkbox is checked. I know it doesn't look right, but I tried everything else I could think of.
And I will totally remember the other option in place of using Iff! That's simpler :-)

Thank you again for taking your time to help me with this!

[CJ_London]

try

AND (([Forms]![frmSheetPlate]![chkOverZero]=-1 AND tblSheetPlate.Qnty_On_Hand>0) OR [Forms]![frmSheetPlate]![chkOverZero]=0)


note the bracketing

[achammar]

That worked! Thank you very very much! I really appreciate that!