carriage returns (1 Viewer)

[teel73]

I have a table with a memo field called "DESC". That memo field gets populated from code. My problem is that I have carriage returns in my code that converts to some type of symbol which is displayed in the field of the table. How do I get the data to populate the field with the carriage returns instead of the symbols?

Below is my code where I set up my variable contaning the text that I want to be populated into the field, then run the update query to update that field.

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblBackgroundReview.Comments FROM tblContractorDataOnly INNER JOIN tblBackgroundReview ON tblContractorDataOnly.cont_dataId = tblBackgroundReview.Data_ID " _
                    & "WHERE (((tblBackgroundReview.BI_ID)=" & BIID & "));", dbOpenSnapshot)
                    
If Not IsNull(rst![comments]) Then
    extComment = Format(Now(), "mm/dd/yyyy hh:nn") & " - " & CurrentUser() & ": Provisional access was extended to " & ext & " " & totExt & " extension(s)" & Chr(13) & rst![comments]
Else
    extComment = Format(Now(), "mm/dd/yyyy hh:nn") & " - " & CurrentUser() & ": Provisional access was extended to " & ext & " " & totExt & " extension(s)"
End If
 
mySql = "UPDATE tblBackgroundReview SET tblBackgroundReview.comments ='" & extComment & "' , tblBackgroundReview.ExtendTo = #" & ext & "#, tblBackgroundReview.totalExtensions = " & totExt & ", tblBackgroundReview.extGiven = Date(), tblBackgroundReview.LastUpdated = Date(), tblBackgroundReview.LastUpdatedTime = Now(), tblBackgroundReview.LastUpdatedBy = CurrentUser() " _
    & "WHERE (((tblBackgroundReview.BI_ID)=" & BIID & "));"
 
DoCmd.SetWarnings False
DoCmd.RunSQL mySql
DoEvents
Form.Requery
DoEvents
DoCmd.SetWarnings True

That codes converts the "Chr(13)" to a little square symbol instead of a carriage return.

 

[KenHigg]

Not sure it would fix anything but I usually add a line feed in there as well- ?

(or just vbCRLF)

 

[The_Doc_Man]

The square symbol is because your chosen font renders the carriage return character that way. When manually entering a line break, you have to use something like SHIFT-RETURN to make a line break inside a text box. Off the top of my head, I don't recall what that translates to, because Access intercepts it. But instead of using Chr$(13) try using vbCrLf as Ken suggested.

 

[boblarson]

One other mention - which Ken briefly touched on. You don't separate the Carriage Return and Line Feed. So, if you want to use Chr(13) it MUST be paired with Chr(10) in order for it to work properly.

Chr(13) & Chr(10) (and it has to be in that order)

or

vbCrLf

 

[teel73]

thanks guys.

 

[pkstormy]

If I need to populate a value with a carriage return, I'll write something like this:

me!Somefield = "This is the first line" & vbcrlf & "This is the 2nd line."

or

me!Somefield = "This is the first line" & vbclrf & "This is the 2nd line."

(I get it mixed up if it's vbcrlf or vbclrf but it will auto-capitolize the correct syntax.)

 

[boblarson]

(I get it mixed up if it's vbcrlf or vbclrf but it will auto-capitolize the correct syntax.)

vbCrLf
(Carriage Return Line Feed)
CHR(13) - Carriage Return
CHR(10) - Line Feed

CHR(13) & CHR(10) = vbCrLf

 

[teel73]

Hey Guys ..

In regards to carriage returns in the value, I have a similar situation. I've imported a text file into a table. One of the fields is a memo field that includes carriage returns(line feeds) but the carriage returns are translated into a symbol. Is there a way I can search for the symbol and enter the "vbCrLf" for line feed.?

 

[teel73]

Hey Guys ..

In regards to carriage returns in the value, I have a similar situation. I've imported a text file into a table. One of the fields is a memo field that includes carriage returns(line feeds) but the carriage returns are translated into a symbol. Is there a way I can search for the symbol and enter the "vbCrLf" for line feed.?

Hey Guys...

I figured it out. Thank you.

 

[DCrake]

If all else fails you could always use vbNewline