Help with expression to get text string from check boxes

kyuball

Registered User.
Local time
Yesterday, 16:41
Joined
Jul 6, 2009
Messages
66
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?
 
I would do it this way, this loops through the detail section looking for check boxes. If it is a checkbox and it is checked, it adds the controls name to a long string in memory. At the end of the loop it sets a text field to the long string and the record saves.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control, MyLongString As String
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCheckBox
If ctl.Value <> 0 Then
MyLongString = MyLongString & "," & ctl.Name
End If
End Select
Next ctl
'Remove the lead comma
Me.LongStringField = Mid(MyLongString, 2)
End Sub
 
Would this particular code work on a continuous form, as well? I know that Form subroutines tend to only affect the first record on continous forms...
 
Thank you so much for your help MMaynard! I am trying it out as we speak.

BTW, is the text box that the string dumps into called "MyLongString"?
 
No, the textbox on my test form is the one named LongStringField
 
Hey MMaynard,

I hate to be a pest, but would you be able to upload that test form? I am still a relative beginner in Access and would love to know how you made it work.

Kyu
 
Hey MMaynard,

I hate to be a bother considering the time and effort that you have given me, but is there any way to get that in an .mdb format? I am still running on MSA 2003 and am unable to open .accdb files...
 
I got back to work and tried it out and it worked awesome! Thanks, MMaynard!

Is there a way to get the last ctl.Name to be preceded by an "and" (like mental illness, domestic violence and chronic medical problem instead of mental illness, domestic violence, chronic medical problem)? I tried firguring it out based on what you had there, but I could not...
 
Ok this was a brain twister but it should work.
Replace the code with this

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control, MyLongString As String, MyLengthToTrim As Integer, MyLeftString As String, MyRightString As String
For Each ctl In Me.Detail.Controls
    Select Case ctl.ControlType
        Case acCheckBox
            If ctl.Value <> 0 Then
                MyLongString = MyLongString & "," & ctl.Name
            End If
    End Select
Next ctl
'Remove the lead comma
Me.LongStringField = Mid(MyLongString, 2)
MyOriginalLength = Len(Mid(MyLongString, 2))
MyLengthToTrim = Len(Mid(Me.LongStringField, InStrRev(LongStringField, ",") + 1))
MyLeftString = Left(Mid(MyLongString, 2), MyOriginalLength - MyLengthToTrim)
MyRightString = Right(Mid(MyLongString, 2), MyLengthToTrim)
Me.LongStringField = MyLeftString & " and " & MyRightString

'Check for a single entry and trim off the AND
If Left(Me.LongStringField, 4) = " and" Then
    Me.LongStringField = Right(Me.LongStringField, Len(Me.LongStringField) - 5)
End If

End Sub
 
Hey MMaynard,

You're a friggin' genius, dude! I am still trying to wrap my head around what you have there but I am starting to get the gist of it.

Also, is there no way of getting rid of the last comma in the LeftString? Right now, it returns two checked conditions as "Mental Illness, and Physical Disability"

BTW, for future users of the above code, remember to add "MyOriginaLength as Integer" to the list of things to dim.
 
Last edited:
Code:
Dim ctl As Control, MyLongString As String, MyLengthToTrim As Integer, MyLeftString As String, MyRightString As String, MyOriginaLength As Integer
For Each ctl In Me.Detail.Controls
    Select Case ctl.ControlType
        Case acCheckBox
            If ctl.Value <> 0 Then
                MyLongString = MyLongString & "," & ctl.Name
            End If
    End Select
Next ctl

'Remove the lead comma
Me.LongStringField = Mid(MyLongString, 2)
myoriginallength = Len(Mid(MyLongString, 2))
MyLengthToTrim = Len(Mid(Me.LongStringField, InStrRev(LongStringField, ",") + 1))

'Remove the comma before AND
If myoriginallength <> MyLengthToTrim Then
    MyLeftString = Left(Mid(MyLongString, 2), myoriginallength - MyLengthToTrim - 1)
Else
    MyLeftString = Left(Mid(MyLongString, 2), myoriginallength - MyLengthToTrim)
End If

MyRightString = Right(Mid(MyLongString, 2), MyLengthToTrim)
Me.LongStringField = MyLeftString & " and " & MyRightString

'Check for a single entry and trim off the AND
If Left(Me.LongStringField, 4) = " and" Then
    Me.LongStringField = Right(Me.LongStringField, Len(Me.LongStringField) - 5)
End If

:)
 

Users who are viewing this thread

Back
Top Bottom