We have a table that details income eligibility for a number of weatherization and utility payment subsidies available to low income families. Eligibility stats change periodically, and each agency operates on it’s own timetable. The goal is to name the household's income eligibility category for each program application by Inquiry Date, and depending on which agency sets the limits for that type of funding.
(( Programs::Agency = Income Eligibility::Agency ) and
( Programs::Total in Household = Income Eligibility::HouseholdSize ) and
((Programs::Monthly Income /12 ≤ Income Eligibility::IncomeLimit ) and (Programs::Monthly Income /12 ≥ Income Eligibility::IncomeBase)) and
(( Programs::Date of Inquiry ≥ Income Eligibility::DateStart ) and ( Programs::Date of Inquiry ≤ Income Eligibility::DateEnd ))
This works great as a portal filter, but what we need for reporting purposes is a calculation field. When I experimented with an If statement, only 1 record in the database registered a value.
If ( all of the above is true ) ; Income Eligibility::IncomeCategory ; "" )
Can someone explain my faulty logic and suggest a fix or an alternate approach?