How to generate a list in an Access Report?

Question

I'm trying to generate a report in Microsoft Access which should make a list based on the content stored in a field called Exams in a Table named Checkup.

So far I have a report with a calculated field and a textbox with record source Exams. The calculated field uses Switch function to generate the list; here's the code:

=Switch([Examenes]="BHC","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:",[Examenes]="BHC, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:",[Examenes]="BHC, EGO, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "GB:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:" & Chr(13) & Chr(10) & "EGO" & Chr(13) & Chr(10) & "Color:" & Chr(13) & Chr(10) & "Densidad:" & Chr(13) & Chr(10) & "Ph:" & Chr(13) & Chr(10) & "SO:" & Chr(13) & Chr(10) & "Proteinas:" & Chr(13) & Chr(10) & "CE:" & Chr(13) & Chr(10) & "LL:" & Chr(13) & Chr(10) & "FM:" & Chr(13) & Chr(10) & "Nitrito:" & Chr(13) & Chr(10) & "Cristales:",[Examenes]="BHC, EGH, EGO, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:" & Chr(13) & Chr(10) & "EGO" & Chr(13) & Chr(10) & "Color:" & Chr(13) & Chr(10) & "Densidad:" & Chr(13) & Chr(10) & "Ph:" & Chr(13) & Chr(10) & "SO:" & Chr(13) & Chr(10) & "Proteinas:" & Chr(13) & Chr(10) & "CE:" & Chr(13) & Chr(10) & "LL:" & Chr(13) & Chr(10) & "FM:" & Chr(13) & Chr(10) & "Nitrito:" & Chr(13) & Chr(10) & "Cristales:" & Chr(13) & Chr(10) & "EGH" & Chr(13) & Chr(10) & "Protozoarios:" & Chr(13) & Chr(10) & "Metazoarios:")

The code works well, however, I need to add more values to the Switch function but I get error message "The text is too long to be edited". Since this field is calculated I can't just go to the table and add more characters or make it into a memo type field (or I least I don't know how).

Is there a workaround to this issue? I've also tried putting the code to onLoad (or OnOpen) event in the report, but haven't gotten it to work. Access gives me Compile error: Expected: list separator or )

Here's the code:

Private Sub Report_Open(Cancel As Integer)
Switch([Examenes]="BHC","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:",[Examenes]="BHC, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:",[Examenes]="BHC, EGO, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "GB:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:" & Chr(13) & Chr(10) & "EGO" & Chr(13) & Chr(10) & "Color:" & Chr(13) & Chr(10) & "Densidad:" & Chr(13) & Chr(10) & "Ph:" & Chr(13) & Chr(10) & "SO:" & Chr(13) & Chr(10) & "Proteinas:" & Chr(13) & Chr(10) & "CE
:" & Chr(13) & Chr(10) & "LL:" & Chr(13) & Chr(10) & "FM:" & Chr(13) & Chr(10) & "Nitrito:" & Chr(13) & Chr(10) & "Cristales:",[Examenes]="BHC, EGH, EGO, VDRL","BHC" & Chr(13) & Chr(10) & "Hto:" & Chr(13) & Chr(10) & "Gb:" & Chr(13) & Chr(10) & "E:" & Chr(13) & Chr(10) & "S:" & Chr(13) & Chr(10) & "L:" & Chr(13) & Chr(10) & "M:" & Chr(13) & Chr(10) & "St:" & Chr(13) & Chr(10) & "B:" & Chr(13) & Chr(10) & "VDRL:" & Chr(13) & Chr(10) & "EGO" & Chr(13) & Chr(10) & "Color:" & Chr(13) & Chr(10) & "Densidad:" & Chr(13) & Chr(10) & "Ph:" & Chr(13) & Chr(10) & "SO:" & Chr(13) & Chr(10) & "Proteinas:" & Chr(13) & Chr(10) & "CE:" & Chr(13) & Chr(10) & "LL:" & Chr(13) & Chr(10) & "FM:" & Chr(13) & Chr(10) & "Nitrito:" & Chr(13) & Chr(10) & "Cristales:" & Chr(13) & Chr(10) & "EGH" & Chr(13) & Chr(10) & "Protozoarios:" & Chr(13) & Chr(10) & "Metazoarios:")
End Sub

Any help is greatly appreciated!

Answer 1

Try using a simple Select ... Case construct:

Private Sub Report_Open(Cancel As Integer)

    Const BHC   As String = "BHC" & vbCrLf & "Hto:" & vbCrLf & "Gb:" & vbCrLf & "E:" & vbCrLf & "S:" & vbCrLf & "L:" & vbCrLf & "M:" & vbCrLf & "St:" & vbCrLf & "B:"
    Const VDRL  As String = vbCrLf & "VDRL:"    
    Const EGO   As String = vbCrLf & "EGO" & vbCrLf & "Color:" & vbCrLf & "Densidad:" & vbCrLf & "Ph:" & vbCrLf & "SO:" & vbCrLf & "Proteinas:" & vbCrLf & "CE:" & vbCrLf & "LL:" & vbCrLf & "FM:" & vbCrLf & "Nitrito:" & vbCrLf & "Cristales:"
    Const EGH   As String = vbCrLf & "EGH" & vbCrLf & "Protozoarios:" & vbCrLf & "Metazoarios:"

    Dim Value   As string

    Select Case Me!Examenes.Value
        Case "BHC"
            Value = BHC
        Case "BHC, VDRL"
            Value = BHC & VDRL
        Case "BHC, EGO, VDRL"
            Value = BHC & VDRL & EGO
        Case "BHC, EGH, EGO, VDRL"
            Value = BHC & VDRL & EGO & EGH
    End Select

    ' Display result.
    Me!YourUnboundTextbox.Value = Value

End Sub