Hard Returns from Excel are Lost

[Bill]

Hi All,

Occasionally I need to copy records from an Excel document that has 'hard
returns' (ALT+Return) within the cell. The spreadsheet is simply copied and
pasted into the Access table, but the returns are loss and I have to go
through them all manually in a form with the field setup as 'new line in
field' to put them back.

Any ideas how I can improve this process please?

Regards.
Bill.

 

[RoyVidar]

Hi All,

Occasionally I need to copy records from an Excel document that has
'hard returns' (ALT+Return) within the cell. The spreadsheet is
simply copied and pasted into the Access table, but the returns are
loss and I have to go through them all manually in a form with the
field setup as 'new line in field' to put them back.

Any ideas how I can improve this process please?

Regards.
Bill.

I think that what you should have got, is some strange "squares"
representing LineFeed (Chr(10)) which is what you get when using
Alt + Enter in Excel Cells.

It might be dependent on which method you're using to get the
information over to Access, though.

You could try to replace occurences of Chr(10) with Chr(13) & Chr(10),
carriage return and line feed.

Replace(TheField, Chr(10), Chr(13) & Chr(10))

- on a copy of the database, of course

 

[Bill]

Hi Roy,

Appol's for the delay in replying. How do I view character codes in Access?

Regards.
Bill.

 

[RoyVidar]

Hi Roy,

Appol's for the delay in replying. How do I view character codes in
Access?

Regards.
Bill.

If I understand your question correct, you can use the Asc() function,
say in the immediate pane (ctrl+g):

? asc("a") -> 97
? asc("A") -> 65
? asc(vbcr) -> 13
TheString = "test"
? asc(Mid(TheString, 2,1)) -> 101 (small letter 'e')