Concatenating strings and carriage returns on report

[alikona]

Hi all,



I have a report that looks at a series of unbound check boxes on my form. The code in my report looks to see which boxes are checked, assigns a text string and then Concatenates those strings in a text box, depending on which boxes are checked, it also looks up some verbiage in a table and concatenates that into another text field.

Private Sub Report_Load()

Dim SU_CoC As String
Dim T_CoC As String
Dim SU_MatCoC As String
Dim T_MatCoC As String
Dim SU_83 As String
Dim T_83 As String
Dim SU_97 As String
Dim T_97 As String
Dim SU_286 As String
Dim T_286 As String
Dim PSIVal1 As String
Dim Sub_Text As String
Dim Verb1_Text As String

PSIVal1 = Nz(Forms![frmCert]![txt97_PSIG], "")

If [Forms]![frmCert].ck_COC = True Then
    SU_CoC = "Certificate of Conformance"
    T_CoC = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'CoC'") & Chr(13) & Chr(10))
Else
    SU_CoC = ""
    T_CoC = ""
End If


If [Forms]![frmCert].ck_MCOC = True Then
    SU_MatCoC = "Material Certification"
    T_MatCoC = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'MatCert'") & Chr(13) & Chr(10))
Else
    SU_MatCoC = ""
    T_MatCoC = ""
End If


If [Forms]![frmCert].ck_83 = True Then
    SU_83 = "S-83"
    T_83 = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'S-83'") & Chr(13) & Chr(10))
Else
    SU_83 = ""
    T_83 = ""
End If


If [Forms]![frmCert].ck_97 = True Then
    SU_97 = "S-97"
    T_97 = (Replace(DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'S-97'"), "PSIVALUE1", PSIVal1) & Chr(13) & Chr(10))
Else
    SU_97 = ""
    T_97 = ""
End If


If [Forms]![frmCert].ck_286 = True Then
    SU_286 = "S-286"
    T_286 = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'S-286'") & Chr(13) & Chr(10))
Else
    SU_286 = ""
    T_286 = ""
End If


Sub_Text = SU_CoC & (", " + SU_MatCoC) & (", " + SU_83) & (", " + SU_97) & (", " + SU_286)
Me.txtSubject = Sub_Text

Verb1_Text = T_CoC & T_MatCoC & T_83 & T_97 & T_286
Me.txtVerb1 = Verb1_Text

End Sub

So my issue is I am attempting to suppress null values by using "+" instead of "&", so that I do not have a bunch of extra commas in my string, its not working as expected. If I only have the CoC box checked, I get "Certificate of Certification, , , , , , " as my field output.

Another issue I am running into is adding carriage returns in my Verbiage field. How I have this set up, in my tblCerts, I have several different tests listed and verbiage that describes the outcome of those tests. I would like there to be a return and empty line after each set of verbiage so similar to this:

Test 69 was executed as expected.

Test 125 was executed as expected.

I have tried a few different alterations of the code to attempt to get this to work, including adding the & Chr(13) & Chr(10) string at the bottom where I define Verb1_Text, I have attempted to replace the & Chr(13) & Chr(10) with vbCrLf but neither produce a carriage return.

Any ideas?

[June7]

If you want a blank line then need two vbCrLf.

Your code can set variables to empty string then you concatenate those strings so you will get orphan commas. Don't set to empty strings, set to Null. Have to declare variables as Variant to allow setting Null.

[Welshgasman]

So the values are not Null?

Have you even checked them? as you are setting them to a ZLS not Null ?

[alikona]

Hmm, I originally had them set up as variant with Null in the Else clause but that was throwing errors. I just switched them back and that appears to be working now. I must have had something else in my code that was broken before.

Still struggling a bit with the carriage returns. I have replaced the Chr(13) & Chr(10) string with vbCrLf & vbCrLf.

Its almost like the text box is suppressing them. They are showing up on separate lines but there is no space between them:

Test 69 was executed as expected.
Test 125 was executed as expected.

If it matters, my text box is formatted as rich text and does have grow/shrink set to yes.

Edit to add, I added a Debug.Print and it does indeed show the extra return in the immediate window but it is not displaying that way on the report.

Edit2 If I go into my tblCerts and add a return at the end of my verbiage, it will show the blank line between when I concatenate them.

[June7]

Two vbCrLf work in a textbox set for Plain Text, not Rich Text. Why do you have set to Rich Text? Rich Text would need HTML tags for line breaks. When you type a return in table long text field set for Rich Text, Access converts to HTML tag <br>.

[Micron]

When you fix the rtf issue, consider typing less, unless you really like typing

Dim SU_CoC As String, T_CoC As String, SU_MatCoC As String, T_MatCoC As String
Dim SU_83 As String, T_83 As String, SU_97 As String, T_97 As String, SU_286 As String
Dim T_286 As String, PSIVal1 As String, Sub_Text As String, Verb1_Text As String
Dim crLfs As String
Dim frm As FORM

crLfs = vbCrLf & vbCrLf
Set frm = [Forms]![frmCert]
PSIVal1 = Nz(frm.[txt97_PSIG], "")

If frm.ck_COC = True Then
    SU_CoC = "Certificate of Conformance"
    T_CoC = DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'CoC'") & crLfs
Else
    SU_CoC = ""
    T_CoC = ""
End If

Fewer Dim's, [Forms]![frmCert], Chr(13) & Chr(10) (or fewer vbCrLf & vbCrLf)

[alikona]

Two vbCrLf work in a textbox set for Plain Text, not Rich Text. Why do you have set to Rich Text? Rich Text would need HTML tags for line breaks. When you type a return in table long text field set for Rich Text, Access converts to HTML tag <br>.

Unfortunately I am unable to change to plain text. This DB replaces (hopefully) dozens of controlled word templates that are different combos of 14 tests. I can replicate the form in Access but I can't change the formatting, there is bolding, highlighting, accent font color, etc.

I was able to get my code to work with replacing the vbCrLf & vbCrLf with a single "<br>" though. The help was much appreciated.

[isladogs]

Create a query to include your rich text field. Add another field PlainText(YourRichTextFieldName). Now you have both versions available