IIf stupidity

[Pharcyde]

I'm trying to run a query to work out whether a student has passed their assessments or not. Testing on a student who has passed Exam, Report and XX, the following query returns incomplete.

acc1: IIf([Ass-Assessments]!AssessmentID="Exam" And [Result]="Pass" And [Ass-Assessments]!AssessmentID="Report" And [Result]="Pass" And [Ass-Assessments]!AssessmentID="XX" And [Result]="Pass","Completed","Incomplete")

=-=-=-=-=-=-=-=-

But, if I re-rig the assessments table, so the student has only taken (and passed) Exam, and then amend the query to:

acc2: IIf([Ass-Assessments]!AssessmentID="Exam" And [Result]="Pass"","Completed","Incomplete")

then it correctly says Completed. I can see its not the best piece of coding in the world but why doesnt it work!?

Thanks loads, Lee

 

[dfitz]

In your first example, all of the conditions must be true before you will get completed. As you are testing the same field for three different values (AssessmentID), you can never have a true outcome. Try changing the And to Or in your statement and it will work.
df

 

[namliam]

acc1: IIf(([Ass-Assessments]!AssessmentID="Exam" And [Result]="Pass") or ( [Ass-Assessments]!AssessmentID="Report" And [Result]="Pass") or ( [Ass-Assessments]!AssessmentID="XX" And [Result]="Pass"),"Completed","Incomplete")

Or better:
acc1: IIf(([Ass-Assessments]!AssessmentID="Exam" or [Ass-Assessments]!AssessmentID="Report" or [Ass-Assessments]!AssessmentID="XX") And [Result] = "Pass"),"Completed","Incomplete")

That should do it. OR if you need this in more than 1 spottry this in the query:
acc1: myEval([Ass-Assessments]!AssessmentID,[Result])

After putting this in a module:

Function myEval(AssID as variant,Result as variant)  as string
    myEval = "Incomplete"
    if (AssID = "Exam" or AssID = "Report" or AssID ="XX" ) and Result = "Pass" then myEval = "Complete" 
end Function

Regards

 

[Pharcyde]

ah ok that worked, sort of. Maybe you can help me with the next part of the problem.

Basically if all three were passed then the caourse is completed. At the moment, Pass=Completed, and Fail=Incomplete.

I need a Completed flag when all three=Pass. Any ideas?

 

[namliam]

what is your table structure like?

Post a (empty) sample of the db?

Regards

 

[Pharcyde]

dont laugh mailman, but how do i do that??

 

[namliam]

Clear the tables.
Compact the DB
Zip it
then create a post like you normaly do but check below. You can attach (zip) files
Attach the file
Submit.

Done

 

[Pharcyde]

cool all done. there ya go

 

[namliam]

How about using a crosstab query to put the results next to each other on one line, like so:

TRANSFORM Max([Ass-Assessments].Result) AS MaxOfResult
SELECT [Ass-Assessments].StudentID
FROM [Ass-Assessments]
GROUP BY [Ass-Assessments].StudentID
PIVOT [Ass-Assessments].AssessmentID;

You can than check if they all are pass....

Regards

 

[Pharcyde]

worked a treat thanks v.much for taking the time and effort to help me mailman

 

[Pharcyde]

Heres the db. THe logic behind the accDate?? Well, its just a table of dates really. The next available accDate needs to be found, and as soon as current date reaches that (if all exams are passed) then the db will accredit them automatically. ( I know hoe to figure that one out) so this is the missing piece of the jigsaw i guess....


is that ok? Cheers, Lee