I have a database that keeps track of special needs of social work clients and I need some help displaying data a string of text based on data entered through check boxes.
After case managers have gathered intake information from a client, they enter the data through a form that allows them to enter basic info (name, dob, etc.) and also clients' special needs through a bunch of check boxes. The form has several special needs categories listed as check boxes (e.g. mental illness, chronic medical condition, domestic violence, etc.) which enters data into a table that keeps track of clients and their information. Once data is entered, case managers can review the information through another form that acts as a profile page.
I wanted to display the special needs as a string of text like "Mental Illness, Substance Abuse and Developmental Disability" based on the check boxes that have been marked as true on the data entry form. Something like the expression below obviously will not account for the various situations that can arise (e.g. when only one box is checked, it leaves out commas and conjunctions or when only two is checked, it leaves out commas and goes directly to conjunctions or not piling up commas when some of the fields inbetween have not marked true etc.)
expression: iif([mh]=true, "mental illness", iif([sa]=true, " ,substance abuse", iif([dv]=true, " and domestic violence"))) (so on and so forth)
However, when I try to account for every possible combination (there are 12 or so listed special needs), it tells me that the expression is either too long or too complicated, but I have no idea how to write any other expression.
Can someone please help?
After case managers have gathered intake information from a client, they enter the data through a form that allows them to enter basic info (name, dob, etc.) and also clients' special needs through a bunch of check boxes. The form has several special needs categories listed as check boxes (e.g. mental illness, chronic medical condition, domestic violence, etc.) which enters data into a table that keeps track of clients and their information. Once data is entered, case managers can review the information through another form that acts as a profile page.
I wanted to display the special needs as a string of text like "Mental Illness, Substance Abuse and Developmental Disability" based on the check boxes that have been marked as true on the data entry form. Something like the expression below obviously will not account for the various situations that can arise (e.g. when only one box is checked, it leaves out commas and conjunctions or when only two is checked, it leaves out commas and goes directly to conjunctions or not piling up commas when some of the fields inbetween have not marked true etc.)
expression: iif([mh]=true, "mental illness", iif([sa]=true, " ,substance abuse", iif([dv]=true, " and domestic violence"))) (so on and so forth)
However, when I try to account for every possible combination (there are 12 or so listed special needs), it tells me that the expression is either too long or too complicated, but I have no idea how to write any other expression.
Can someone please help?