New Line

[Yvaine]

I have to get a cell's value from an excel spreadsheet and store into an access table. However, the cell's value contains multiple lines, which means there is a new line character at the end of each line. Hence, when i store the value into access table, once it reaches the new line, it will not store the remaining characters. I actually want to keep the existing structure of the value in the access table because my report of this table should display as the what is shown in the cell.

For example of a value of a cell:

Fruits sold in Malaysia:
1) Apple
2) Oranges

[OBP]

Have you set the Access Table's Field type to "Memo"?
that will allow as many lines and as much text as you want.

[Yvaine]

Yes, I have set it to memo. It does allow storing more than 255 characters. However, when there is new line, it will be stored as a little square. So when i display the report, the layout will not be as the original. Instead, it will display as one line with little square character that indicates new line.

[OBP]

Yes it will do that, the text needs converting with a Macro to change the small square to a "New Line" character.
However it does point to the fact that your database design is not correct, basically all you have done is reproduce what you had in Excel, rather than taking advantage of Access's Relational database design facilities.
The example that you gave of
"Fruits sold in Malaysia:
1) Apple
2) Oranges"

The Country Malaysia should be in one Table and the Fruits should be in a Seperate table that is "related" to the Country table via an ID field.


http://www.vbaexpress.com/forum/images/buttons/quote.gif (http://vbaexpress.com/forum/newreply.php?do=newreply&p=150827)

http://www.vbaexpress.com/forum/images/buttons/quote.gif (http://vbaexpress.com/forum/newreply.php?do=newreply&p=150827)

[Yvaine]

I got the solution. Before storing the excel value containing line breaks into access table, we have to replace Chr(10) which is the line break with chr(13) and chr(10). For example:

information = WkBk.Worksheets("Sheet1").Range("T" & intI + 14).Value
information = Replace(information, Chr(10), Chr(13) & Chr(10))
rst1.Fields("Info") = information