vblf's missing

[mikeo1313]

I got external excel data into an access database.

my problem is that it seems like the vbLF's are gone. I have to explicitly copy data from one cell of excel to access for the linefeed to show, 1 by 1.

Do vblf's & memo fields not mix?

Do vbcrlf's work out better???

HOW CAN I convert all my VBlf's in a column to VBcrlf's???

 

[LPurvis]

Your suspicion is correct.
It's not that there's a problem with the Memo field though.
It's simply the Access UI itself, whose controls don't recognise the single LineFeed as such. They do indeed demand a Carriage Return and Line feed... vbCrLf or Chr(13) & Chr(10).

Depending upon the process you use to acquire the data it could be very simple or a tiny bit of convolution.
Ultimately you can replace the incoming text
Me.ControlName = Replace(strText, vbLf, vbCrLf)

Bear in mind that we're talking only about the Access UI here.
CRLF will be respected as such in other applications too, but if you inadvertently replace the LF in a pre-existing CRLF then you will effectively end up with a double return that renders as single in the Access UI.
(You can prevent this by checking for or changing pre-existing CRLFs).

Cheers.

 

[mikeo1313]

Thanks but I don't understand how to execute this statement:
Me.ControlName = Replace(strText, vbLf, vbCrLf)


Say for instance my table name is "tblrec" and the column is "list"

 

[LPurvis]

If you wanted to do this as a query, something like:

UPDATE tblrec
SET

• = Replace(Replace(
  • , Chr(13) & Chr(10), Chr(10)), Chr(10), Chr(13) & Chr(10))
    WHERE
    • Like "*" & Chr(10) & "*"
      
      Cheers.