Concatenate checkbox values into a text field on a report (1 Viewer)

Bombshell Disaster · Jan 31, 2011

Hi All

I am trying to concatenate 13 checkboxes into a text field for reporting.

I know that I can add the following to the ControlSource ID on a text box in the report.

Code:

=[BloodTest] & (", " + [BowelPrep]) & (", " + [DietReq]) & (", " + [MedReq])

Though what I am wanting to test is the following, but can't get the syntax to join on to the previous value:

Code:

=IIf([BloodTest]=True,"Blood Test",IIf([BowelPrep]=True,",  BowelPrep"))

I think what I need to do in the code window is to make a string that will concatenate the fields with a ", " if the checkbox is = True

Can anyone point me in the right direction.

Many thanks...

Bombshell Disaster · Jan 31, 2011

Hi

I have managed this part of the code:

Code:

Dim i As Integer
Dim lngLen As Long
Dim strWhere As String

For i = 1 To 11
If Me("chk" & i).Value Then
strWhere = strWhere & i & ","
End If
Next

Me!txtMedical = strWhere

lngLen = Len(strWhere) - 1 'Without trailing comma
If lngLen > 0 Then
    Me![txtMedical] = Left$(strWhere, lngLen)
End If

I now just need to see the text values instead of the number appearing:

Instead of 1,3,5 would like to see Blood Test, Bowel Prep, Medical

Any help would be greatly appreciated.

Kafrin · Jan 31, 2011

One way is to write a function in code that looks at the check boxes and that you then call either in a control on the report or in the recordsource behind the report. The code should be in a stand-alone module. In this case I'd probably work on something like this:

Code:

Public Function RepString(ParamArray Tests() As Variant) As String
'Tests() requires pairs of values - the True/False from the check box and the string (name) that corresponds to it.
    Dim S As String, Ctr As Byte
    
    S = ""
    
    For Ctr = LBound(Tests) + 1 To UBound(Tests) Step 2
        If Tests(Ctr-1) = True Then
            If S <> "" Then S = S & ", "
            S = S & Tests(Ctr)
        End If
    Next Ctr
    
    RepString = S
End Function

Then to call the function you'd use this syntax:
RepString([BloodTest], "Blood Test", [BowelPrep], "Bowel Prep", [DietReq], "Dietary Requirements")
Just keep adding in the pairs until they're all there.

If you need to produce this string in many different places, there will be a neater way of doing it, but if it's just for this one report then this code should run quickly and easily.

Bombshell Disaster · Jan 31, 2011

Thank you Kafrin, really appreciate it!

Christopher770 · Sep 3, 2014

Thank you - I know this was posted many moons ago, but I've been look for an answer to this for days.

edwardsashlee1 · Jan 21, 2019

Thanks! This helped me out alot!

Concatenate checkbox values into a text field on a report (1 Viewer)

Bombshell Disaster

Registered User.

Bombshell Disaster

Registered User.

Kafrin

Database Designer

Bombshell Disaster

Registered User.

Christopher770

Registered User.

edwardsashlee1

New member

Similar threads

Users who are viewing this thread