Using combo box to control query / filter results

[TomM484]

I am trying to use a combo box (named GTLT or "greater than less than") to sort my results based on if the Formula Weight ([FW]) is greater than or less than the PointValue given in another text box.

It seems that I am having some trouble using an iif statement here, because it will not let me change the query criteria based on if the GTLT = "greater than" or not. Here is what I have written :

IIf([Forms]![Search Type]![GTLT]="greater than",([CompoundID]![FW]>=[Forms]![Search Type]![PointValue]),([CompoundID]![FW]<[Forms]![Search Type]![PointValue]))

All I want it to do, is take the value given by PointValue, and return all results that are either greater than it (if the statement is true), or less than it (if the statement is false). Access does not seem to like this. Please let me know if you have any ideas.

[Rabbit]

Please show us the full SQL.

[TomM484]

This is what MS Access gave me in SQL View for the query in question:

SELECT CompoundID.CpdID, CompoundID.sortas, CompoundID.CAS, CompoundID.type, CompoundID.FW, CompoundID.BP
FROM CompoundID
WHERE (((CompoundID.FW)=IIf([Forms]![Search Type]![GTLT]="greater than",([CompoundID]![FW]>[Forms]![Search Type]![PointValue]),([CompoundID]![FW]<[Forms]![Search Type]![PointValue]))));

Tom

[Rabbit]

Please use code tags when posting code.

It makes no sense to see if FW is equal to true/false. Which is what your IIf function returns. Just have it return the trues.