Carriage returns getting stripped from text field when viewed in Access.

I have a form in an application that was typed up as follows;
Should be: 375 thick x 8.5 x 8.25 (1 piece)
Actual: .354 thick x 8.5 x 8.25
Material to thin to clean up at grinding
Ref PO 18112 line item 8


But when I connect to that table\field in access and try to place that text field on a report it displays as;
Should be: 375 thick x 8.5 x 8.25 (1 piece) Actual: .354 thick x 8.5 x 8.25 Material to thin to clean up at grinding Ref PO 18112 line item 8

It seems access is stripping out the carriage return or line feed from the text? Anyone know how to get access to reflect the carriage returns as the text was typed up in the original application?

The funny thing is, when I tried to copy the text from access (no carriage returns) and pasted it in this web browser it pasted down correctly (with the carriage returns).

Thanks in advance for the help!
LVL 2
SeyerITAsked:
Who is Participating?
 
Evan CutlerConnect With a Mentor Volunteer Chief Information OfficerCommented:
that's because the query window is treating vblf as a field: [vblf]
You want this:

NEWTEXT: SUBSTRING([Description], 1, 10) & vbcrlf & SUBSTRING([Description], 11, 10)

that gives you 10 characters then carraige return then 10 characters.
you can do the same thing in words...
but I'm away from my computer...I'll get that to you in a bit.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
carraige returns are not recognized in text.


Use the TextBox's After Update event procedure to add the vbCrLf:

If Not IsNull(Me!txtBody) then
If Right$(Me!txtBody,2) <vbCrLf Then
Me!txtBody = Me!txtBody & vbCrLf
End If
End If
0
 
Rey Obrero (Capricorn1)Commented:
when you enter the information in the text box instead of just hitting the Enter key,
hold the Ctrl and press the Enter key

see if it will work
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SeyerITAuthor Commented:
I probably should have clarified that I cannot edit this data.. It's just being read from a SQL connection.

I was thinking perhaps I could just put a formula in my query and have it read the field differently.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
the problem then is that the form needs to put the crlf into SQL for your report to know it is to be there.

If you look at the straight sql and there are no symbols to tell it there was a crlf, then there is nothing you can do.

The other option, if you want.  Is to do a SUBSTR on the string, count the words, and insert a crlf on the end of the last word before X characters.

That will simulate a CRLF for you.
0
 
SeyerITAuthor Commented:
I created the view in SQL.. Could I change the way I added that field to the View so that it will have a symbol there?

The other option, if you want.  Is to do a SUBSTR on the string, count the words, and insert a crlf on the end of the last word before X characters.

I'm not sure this will work as the information in the text boxes always varies.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Access is not stripping anything out.  The problem is that Access needs a carriage return and line feed pair x(0D0A) to give you a new line in a control.

Your original app is suppling one or the other, but not both.  Try defining a new field as:

Replace([<fieldNameHere>], vbCr, vbCrLf)

or

Replace([<fieldNameHere>], vbLF, vbCrLf)

You should get multiple lines then.

Jim.
0
 
SeyerITAuthor Commented:
It isn't recognizing vbLf, vbCr or vbCrLf in my query.. Here's the screenshot;

error
0
 
SeyerITAuthor Commented:
Oh and I tried it this well as well;
NewText: Replace([dmcaDescription],Chr(10),Chr(10) & Chr(13))

The query runs but it doesn't add in any carriage returns..
0
 
SeyerITConnect With a Mentor Author Commented:
NewText: Replace([dmcaDescription],Chr(13),Chr(13) & Chr(10))

Got it! ^^^

The above code did the trick!
0
 
SeyerITAuthor Commented:
The formula I listed works as well
0
All Courses

From novice to tech pro — start learning today.