Simple IIF Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joanne - 11 Jan 2006 23:04 GMT

I created a new field in a query called "Class1" and entered the following
IIF statement. What I want the IIF statement to do is enter the word
"Footnotes" if the two criteria are met, but not add anything if the criteria
are not met. The query also returns the first and last name of the person
who has met the criteria. The problem is that if a certain person meets the
criteria their name comes up twice on the result set. I'd only like to see
their name once i.e. if the criteria is met.

IIf([QNUM]=30 And [Final]="Incorrect","Footnotes","")

Reply to this Message

Douglas J. Steele - 11 Jan 2006 23:35 GMT

What's the SQL of your query? (If you're not familiar with SQL, open the
query and select "SQL View" from the View menu. Copy everything that's
there, and paste it into your reply)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I created a new field in a query called "Class1" and entered the following
> IIF statement. What I want the IIF statement to do is enter the word
[quoted text clipped - 8 lines]
>
> IIf([QNUM]=30 And [Final]="Incorrect","Footnotes","")

Reply to this Message

Jerry Whittle - 12 Jan 2006 16:39 GMT

Like Doug said, show us the SQL! ;-)

To get rid of the dupes try changing the start of the SQL from SELECT to
SELECT DISTINCT.

You just might need a nested IIf statement. It would first evaluate if
[QNUM]=30 then what to do if [Final]="Incorrect".

IIf([QNUM]=30, IIf([Final]="Incorrect","Footnotes",Null), Null)

Noticed that I changed the last "" (empty string) to Null. Actually this
won't fix the problem, rather it's just a personal adversion of mine to empty
strings. So feel free to tell me to jump in a lake.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I created a new field in a query called "Class1" and entered the following
> IIF statement. What I want the IIF statement to do is enter the word
[quoted text clipped - 5 lines]
>
> IIf([QNUM]=30 And [Final]="Incorrect","Footnotes","")

Reply to this Message

Joanne - 12 Jan 2006 18:57 GMT

Thank you very much for your help. Here is the SQL statement and even with
SELECT DISTINCT I still get this extra row of the user first name and last
name as well as the blank Class 1 field.

SELECT DISTINCT Scores.LAST, Scores.FIRST,
IIf([QNUM]=30,IIf([Final]="Incorrect","Footnotes",Null),Null) AS Class1
FROM Scores INNER JOIN QnAllTable ON Scores.INDEX_NUM = QnAllTable.INDEX_NUM
GROUP BY Scores.LAST, Scores.FIRST, QnAllTable.QNUM, QnAllTable.FINAL
ORDER BY Scores.LAST;

> Like Doug said, show us the SQL! ;-)
>
[quoted text clipped - 19 lines]
> >
> > IIf([QNUM]=30 And [Final]="Incorrect","Footnotes","")

Reply to this Message

John Spencer - 12 Jan 2006 19:55 GMT

Well, Class 1 is probably the culprit, since it could calculate at FootNotes
in one case and Null in another case. Therefore, two records are returned.
Also, I wonder that the query (as posted) even ran, since you didn't group
by Class1. It's hard to say as I don't know which table or tables have the
fields QNUM and Final. By the way, I simplified the Class1 calculation.

What do you want to happen when two different values are calculated for
Class1?

SELECT DISTINCT Scores.LAST, Scores.FIRST,
IIf([QNUM]=30 AND [Final]="Incorrect","Footnotes",Null) AS Class1
FROM Scores INNER JOIN QnAllTable ON Scores.INDEX_NUM = QnAllTable.INDEX_NUM
GROUP BY Scores.LAST, Scores.FIRST, QnAllTable.QNUM, QnAllTable.FINAL,
IIf([QNUM]=30 AND [Final]="Incorrect","Footnotes",Null) AS Class1
ORDER BY Scores.LAST;

> Thank you very much for your help. Here is the SQL statement and even
> with
[quoted text clipped - 37 lines]
>> >
>> > IIf([QNUM]=30 And [Final]="Incorrect","Footnotes","")

Reply to this Message