Access Query Criteria Creating Parameter Query

0
votes

I am having a problem with a query that uses iif statements per the below:

Items to Exclude: IIf([Is this FAC]="No",[ID2],IIf(([Is this FAC]="Yes") And ([ID2]="CsaId"),"False",[ID2]))

The above 'Items to Exclude' field should populate ID2 where the 'Is this FAC' field is (1) No OR (2) is Yes AND ID2 is not 'CsaId'.

When the Query is run with the above formulas and as per the below it works fine, returning FALSE where it should in the 'Items to Exclude' field per the formula.

enter image description here

The problem is if I then enter <>"False" in the Criteria field per the below (as I want to then filter out any exclusions, i.e. False items)

enter image description here

it seems to turn the field into a parameter query and gives a window pop up per the below instead of just filtering out the FALSE statements.

enter image description here

I would appreciate any help/ideas.

Thanks

1
ms-accessms-access-2013

1 Answers

1
votes

Unfortunately in WHERE and GROUP BY clauses you cannot use column aliases like you did. Replace all Is this FAC by formula for this column. So, in your case for Items to Exclude the formula will be

Items to Exclude: IIf(IIf([Id1]="FAC","Yes","No")="No",[ID2],
    IIf((IIf([Id1]="FAC","Yes","No")="Yes") And ([ID2]="CsaId"),"False",[ID2]))

After this condition will work.You can think about simplifying this formula.

Also you can save your query without criteria and then use it as subquery for filtering out unnecessary records.