Text field missing carriage returns

[jlindquist23]

I'm pulling data from an ODBC table in Sage 100. When the data is viewed in a report in Access, all the carriage returns are ignored and all the text runs together.
Is there a way to resolve this? When I use this same data in Crystal Reports, it prints as it looks in Sage 100. All 3 examples shown below.

SAGE text:




Crystal Reports:


MSAccess:

[Welshgasman]

I would look to see what characters are meant to be the linefeed/carriage return.
Then replace those with vbCrLf

[Micron]

To find out what those characters are, you might need to paste the original content into Word. I think it's Show/Hide in the paragraph group on the ribbon that will show you hidden characters.

[CJ_London]

pass your string to this function and see results in the immediate window

Function AscChar(s As String, Optional startpos As Long = 1, Optional maxchars As Long = 0)
Dim i As Long


    If Len(s) > 0 Then
    
        If maxchars = 0 Or (maxchars + startpos) > Len(s) Then maxchars = Len(s) - startpos
        For i = startpos To startpos + maxchars
            Debug.Print Mid(s, i, 1) & " - " & Asc(Mid(s, i, 1))
        Next i
        
    End If
    
End Function

[jlindquist23]

Adding to Word:


Using the function with a control in the report, the output is:

[Welshgasman]

I cannot see how that function produced that output?

[jlindquist23]

I'm using this in my query:
Setup Instructions: Replace(Replace([ExtendedDescriptionText],Chr(13),Chr(13) & Chr(10)),Chr(10),Chr(13) & Chr(10))
Can Grow and Can Shrink are set at Yes.
Text format is set at Plain Text.
The only issue now is that it truncates the information:

[CJ_London]

It would if they used the maxchars parameter of 6

OP should really show the code they used

[Micron]

Except your code uses dash as a separator but the results show = sign?

[Welshgasman]

* is 42, where is the rest?

[CJ_London]

Good spot 😀

[jojowhite]

maybe create a Function in a Module:

Public Function FixCrLf$(Byval sText$)
sText = Replace$(sText,Chr$(13), Chr$(13) & Chr$(10))
sText = Replace$(sText,Chr$(10), Chr$(13) & Chr$(10))
Do While Instr(1, sText, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)) <> 0
    sText = Replace$(sText, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10), Chr$(13) & Chr$(10))
Loop
FixCrLf = sText
End Function

From your Query:

Setup Instructions: FixCrLf([ExtendedDescriptionText] & "")

[jlindquist23]

I ended up starting over and creating a new report with the same data and settings I entered above and it stopped truncating.
Thank you for your help everyone!