It's hard to know what would work without knowing more about what you are
doing. If you are stating the criteria you will be limited to records that
fulfill the criteria. If the criteria is "Level 1 Pass" there is no need to
test for anything else since it won't be part of the recordset, so a nested
IIf will be of no use.
Are you saying that both fields can contain the values Level 1 Pass, Level 2
Pass, or Level 3 Pass, and no other options? If both fields contain "Level
2 Pass", is the result supposed to be "Full Award"? What if one field
contains "Level 1 Pass" and the other "Level 2 Pass"? Remember, if your
criteria for one of the fields is Level 1 Pass, you will see no records
where that field contains another value.
Remember, I can't see your database, and am not familiar with the real-world
situation you are trying to address. In answer to your question, a nested
IIf may work, but I don't know what result you expect with the various
combinations of field values.
If it is unclear what I am asking about the query, it may be best to post
the SQL. Click View > SQL, copy what you see there, and paste it into a
message.
K11ngy said:
Thansk for info
They are both look up fields
If in the criteria in both fields I state level 1 pass, when I run the
query
and get the paremeter questions I type in level 1 pass and level 1 pass
again
and I get the result "Full award)
Just wondering if a nested function (iif) would work for all 3 levels?
Thanks again
Steve
BruceM said:
If you mean [aon portfolio result] and [aon exam result] are lookup
fields,
that could be your problem. Lookup fields can display one value while
storing another, so if you attempt to work with the displayed value you
may
not get the results you want. More information here:
http://www.mvps.org/access/lookupfields.htm
If you mean something else, please be clearer in describing it.
Try creating a field to test just one of the results. For instance:
IIf [aon portfolio result] = "Level 1 Pass","OK","")
Note the use of an empty string instead of Null. There is some more
discussion of Null here:
http://allenbrowne.com/casu-12.html
The expression may work with Null, but Null is more like "unknown" than
"nothing". Better to use an empty string in such a case as you have
described.
K11ngy said:
Me again
I have kind of solved problem? The 2 fields are both look ups, i.e
level
1
pass, level 2 pass level 3 pass which is why i assume it is acting like
paraemeter query
What I need in the if statement is something like:
= level 1 pass and level 1 pass "full award", null
and the same if it applies to level 2 pass or level 3 pass
Assume nested function but beyond my brain?
Thanks again
Steve
:
Im back again, if anyone can help
I have 2 fields that when the result is seleceted as below
Level 1 pass (First field) and level 1 pass (2nd field)
I want the result "Full award" or Null
I have tried the following statement below
Result: IIf(([aon portfolio result]="Level 1 pass" And [aon exam
result]="Level 1 pass"),"Full Award",Null)
It will run the query but only after acting as a parameter query, but
will
not show the result full award ehwn the criteria is met
Help
