Simple IIF Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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","")
 
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)
 
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.
 
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;


Jerry Whittle said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Joanne said:
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","")
Click to expand...
Click to expand...
 
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;


Joanne said:
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;


Jerry Whittle said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Joanne said:
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","")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top