I am having an issue with access 2007 iif expression.

[J Dillard]

I am trying to issert an iif expression in a query that looks at a field in another query. I need to have anything that is an "N" return a value of FALSE and anything that is "Y" return a value of TRUE.

Here is my expression:

Job Lot Layered with OSS: IIf([iqry00021 Add HOVBU]![JOBLOTLAYEREDWITHOSS]= ”N”, ”False”, ”True”)

[beacon]

Hi J,

I would just include the expression in the [iqry00021 Add HOVBU] query itself as an extra field? That way every record identifies if it is "True" or "False" without having to mess with trying to refer to a separate query (which I don't think is possible).

Plus, by having the expression in the query where the other data resides, you can do other things in future queries, like sum the number of True's, without having to create other queries or expressions.

[Stewart Ross]

Hi. There are two problems with your approach. Firstly, you cannot refer to a field within another query unless you either join that query to the existing one on some common field or fields, or you lookup its value using the DLookup function. Secondly, in an IIF the constants False and True are not enclosed in quotes.

You don't actually need the IIF in any case, since the test for the value being equal to "Y" already returns False or True as a result. Assuming that the query you want to look up is not already joined to the current one, your expression becomes something like:

Job Lot Layered with OSS: Nz(DLookup("[JOBLOTLAYEREDWITHOSS]", "[iqry00021 Add HOVBU]"), "N") = ”Y”

I have used Nz here to substitute "N" in the event that the field value is null, as this would otherwise cause a run-time error.

-Stewart

PS Beacon's post and mine crossed, but I agree with him entirely. It s always better if at all possible to include the fields you need by joining the queries properly.