Must be a better way to manage a text box

[vapid2323]

I have a text box thats getting just a bit more complex than I like:

="REMOVED classifies audit observations as Critical; Major; Minor; and Comment as defined below. The audit revealed " & _
IIf(fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 1,False)=0,"",fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 1,False) & " Critical, ") _ 
& IIf(fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 2,False)=0,"",fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 2,False) & " Major, ") _
& IIf(fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 3,False)=0,"",fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 3,False) & " Minor Observations and ") _
& IIf(fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 4,False)=0,"",fTableRecordCount("tblObservation","WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 4,False) & " Comment(s)")_
 & " which will require response from the " & [centerName] & " site within 14 days of receipt of this report."

This translates into:

[FONT=Calibri]REMOVED classifies audit observations as Critical; Major; Minor; and Comment as defined below. [/FONT]
[FONT=Calibri]The audit revealed [/FONT][B][U][COLOR=#4f81bd][FONT=Calibri]3 Minor[/FONT][/COLOR][/U][/B][FONT=Calibri] Observations which will require response from the [/FONT]
[B][U][COLOR=#4f81bd][FONT=Calibri]«centerName»[/FONT][/COLOR][/U][/B][FONT=Calibri] site within 14 days of receipt of this report.  [/FONT]

The issue is I cant wrap my mind around all the fomatting, adding commas/and where needed and what not.

There MUST be a better way, or am I stuck doing this? I attempted VBA with the on load event but I keep getting errors from the below code so I got stuck.

strCritical = fTableRecordCount("tblObservation", "WHERE [fk_QualificationID]=" & [tblQualification.QualificationID] & "AND [fk_ObsClassification]=" & 1, False)
strMajor = fTableRecordCount("tblObservation", "WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 2, False)
strMinor = fTableRecordCount("tblObservation", "WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 3, False)
strComment = fTableRecordCount("tblObservation", "WHERE [fk_QualificationID]=" & [QualificationID] & "AND [fk_ObsClassification]=" & 4, False)

It says it cant find the [QualificationID], I dont normally do VBA in my reports all that much so any help would be great!

 

[Beetle]

Might be easier to handle the entire output in a function like the following (untested);

Public Function fObvservations(lngQualID As Long, strCenterName As String) As String
    Dim strCritical As String
    Dim strMajor As String
    Dim strMinor As String
    Dim strComment As String
    Dim strResult As String
    
    strCritical = Nz(DCount("*", "tblObservation", _
                 "[fkQualifaicationID]=" & lngQualID _
                & " And [fk_ObsClassification] = 1"), "")
    If strCritical <> "" Then
        strCritical = strCritical & " Critical"
    End If
         
    strMajor = Nz(DCount("*", "tblObservation", _
                 "[fkQualifaicationID]=" & lngQualID _
                & " And [fk_ObsClassification] = 2"), "")
    If strMajor <> "" Then
        If strCritical = "" Then
            strMajor = strMajor & " Major"
        Else
            strMajor = ", " & strMajor & " Major"
        End If
    End If
                
    strMinor = Nz(DCount("*", "tblObservation", _
                 "[fkQualifaicationID]=" & lngQualID _
                & " And [fk_ObsClassification] = 3"), "")
    If strMinor <> "" Then
        If strCritical = "" And strMajor = "" Then
            strMinor = strMinor & " Minor"
        Else
            strMinor = ", " & strMinor & " Minor"
        End If
    End If
          
    strComment = Nz(DCount("*", "tblObservation", _
                 "[fkQualifaicationID]=" & lngQualID _
                & " And [fk_ObsClassification] = 4"), "")
                
    If strComment <> "" Then
        If strCritical = "" And strMajor = "" And strMinor = "" Then
            strComment = strComment & " Comment(s)"
        Else
            strComment = ", " & strComment & " Comment(s)"
        End If
    End If
                
    strResult = "REMOVED classifies audit observations as " _
              & "Critical; Major; Minor; and Comment as defined below." & vbCrLf _
              & "The audit revealed "
              
    strResult = strResult & strCritical & strMajor & strMinor & strComment
    strResult = strResult & " observations which will require response" _
                          & " from the " & strCenterName & " site within" _
                          & " 14 days of the receipt of this report."
                          
    fObservations = strResult
              
End Function

Then call the function like;

=fObservations([QualificationID], [CenterName])