Failing IIF query criteria

[Bazza]

Hi All

Can anyone help me with an IIF statement that is going wrong please?

In a query, I have the following IIF statement in the criteria of a long integer field:

IIf([Forms]![frmSupplierNCRsReviewSelection]![fraErrorCode]=1,>0, In (1,5,6,7))

The IIF statement is getting and using the value fraErrorCode (an option group on a dialogue form) just fine - if I change the IIF to say 'if fraErrorCode = 1, return 3, else return 4' - it returns all records containing value of 3 if the first option in the option group fraErrorCode is chosen, and selecting the second option in the group returns all records containing 4 in the ErrorCode field.

If I use a criteria of >0 in the query I get all records, if I use a criteria of In(1,5,6,7) I get all records that contain one of these values.

As soon as I put them in the IIF statement as above, it fails - the query returns no records.

I've tried lots of things to make this work (giving "([tblSupplierNonConformances].[Error Code])>0" intead of just >0, using switch or choose, Eval(>0) in case Access interprets >0 as text when in an IIF statement), to no avail.

Could someone tell me What am I doing wrong please?

 

[Galaxiom]

Study the syntax of the IIF function again.

The second and third arguments are the values to be used for the True and False outcomess of the condition in the first argument.

You have used meaningless fragments of expressions.

 

[John Big Booty]

This link may help.

 

[Bazza]

Hi Galaxiom, Big John Booty

Thanks for your response.

I think that I may not have stated my problem very clearly!

I have a table (tblSupplierNonConformances) which contains a long integer field ([Error Code] - the foreign key in a relationship to an error codes table) and the data in this field is one of a range of values from 1 to 9.

I want to create a query that looks at a form (frmSupplierNCRsReviewSelection - a dialogue form to allow the user to choose which records are in a report that is to be based on the query) to get a value from the option that the user chooses in an option group (fraErrorCode) on that form. There are only 2 options that the user can choose from - option one returns 1 and option two returns 2.

If the user selects option one, I want the query to return all records in the table, if the user selects option two, I want the query to return only those records where the [Error Code] contains one of the following values: 1 or 5 or 6 or 7

I thought that it should be possible to do this with an IIF statement in the criteria section of the query in the column for the field [Error Code], but I am unable to phrase an IIF that returns the records that I want. Should it be possible to achieve this with an IIF statement here, and if not what would be the best way to approach the problem?

Thanks

 

[wiklendt]

IIf([Forms]![frmSupplierNCRsReviewSelection]![fraErrorCode]=1,>0, In (1,5,6,7))
...
"([tblSupplierNonConformances].[Error Code])>0"

have you tried the fraErrorCode before the zero without the parentheses?

e.g.,

IIf([Forms]![frmSupplierNCRsReviewSelection]![fraErrorCode]=1,[tblSupplierNonConformances].[fraErrorCode]>0, In  (1,5,6,7))

sorry, i've never used In() before, so not sure how to throw that into an iif.

by the way, have you got tblSupplerNonConformances in the query design view? (if you are using it).

 

[Galaxiom]

IIF is used to set the value in a field.
It cannot be used in the criteria box (WHERE clause) of a query.

 

[wiklendt]

IIF is used to set the value in a field.
It cannot be used in the criteria box (WHERE clause) of a query.

ah, this was so obvious to me i forgot to mention it... you're absolutely right.

see the screenshot for clarity:

 

[John Big Booty]

IIF is used to set the value in a field.
It cannot be used in the criteria box (WHERE clause) of a query.

Sorry but that is not entirely correct, the following works very nicely;

SELECT TBL_States.StateID, TBL_States.State, TBL_States.StateName, TBL_States.STD
FROM TBL_States
WHERE (((TBL_States.StateID) Like IIf(IsNull([forms]![form1]![frame4]),"*",[Forms]![form1]![frame4])));

 

[gemma-the-husky]

think about your query logically

what you want is

if the filter flag is 1, then show anyvalue greater than 0
if the filter flag is not 1, then show anyvalue meeeting given numbers

these are two sets of criteria - so an easy way to do this is to entered them on two rows of the query design criteria grid, so the sql ends up looking like


select somestuff where (first set of criteria is true) or (second set of criteria is true)


as Galaxiom pointed out, this is not what an iif statement does at all

 

[Galaxiom]

Sorry but that is not entirely correct, the following works very nicely;

WHERE (((TBL_States.StateID) Like IIf(IsNull([forms]![form1]![frame4]),"*",[Forms]![form1]![frame4])));

Fair enough clarification John. And to generalise, any expression can be used as criteria comparison provided it can be evaluated before the record interrogation begins.

But it cannot include a value from current record in the query as Bazza was trying to do.

 

[John Big Booty]

.........

But it cannot include a value from current record in the query as Bazza was trying to do.

Indeed, Point taken

 

[Bazza]

To all of you, thanks for devoting some of your time to help me.

... any expression can be used as criteria comparison provided it can be evaluated before the record interrogation begins.

But it cannot include a value from current record in the query as Bazza was trying to do.

So I'll go and play bearing this information in mind, perhaps a query before this one so it gets evaluated first, and bring this query into the main query.

Otherwise i'll have to find another way round it to achieve what I need!