I am creating a school database where a result card need to be prepared in report-builder. Detail portion shows subjects with marks obtained, total marks, percentage and grade. Each subject has a grade depend on its percentage. In the footer section I have to show the summary of all subjects and to declare the pass or fail for the student. If a student has even one subject with a grade ‘F’ (Fail) out of seven subject entries, he will be consider failed. Now how I will enter IF or Case When statement for this condition? The problem is I have to refer detail portion’s dataset and column GRADE. Like IF
GRADE contains ‘F’ THEN “FAIL” ELSE “PASS”.
Not certain what your attempted output is, but have attached a sample Base file with a simple report. The report examines each detail line and determines if any grade for a student is a fail mark. If so, this is saved using a ‘Function’ (please see this post for a bit more on functions → How to reuse an accumulated value in a Report?).
Now your question deals with IF statements. Here is one used in the sample:
IF(Test is TRUE; THEN; ELSE). The statement above shows a nested IF. Spent some time on this as there seems to be a bug of some sorts. Got it completely working by also using an
Initial Value in the function.
Sample ----- StudentReport.odb
Note this uses a Firebird embedded DB. Testing with HSQLDB embedded produces incorrect results although set the same. There is definitely a problem with the IF statement.
Thank you for quick reply. I shall adopt it with little modification to my design.
Hi there! I tried to implement the provided solution on more data and found that it works only if ‘F’ placed on middle or last. But if ‘F’ comes on top it gives it 'Pass" and that is wrong. I tried a simple query
IF([Grade=‘F’;“Failed”;“Passed”). It looks like the report read only the last record. My grade column have the data like A,B,C,D or F. so I added in query the order by clause on Grade and the ‘F’ always come on last so it worked.