Importing to a Memo Field from Excel (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:39
Joined
Apr 27, 2015
Messages
6,280
Spent half a day on this one and decided to share my findings just in case someone else runs into this problem.

A web based application I use everyday has the ability to extract large amounts of data - in Excel!

One of the cells is rather large and you can see that there are hard returns (line breaks) in the text. When I import the spreadsheet into Access, the returns seem to get stripped.

What made things really confusing is that when I copied and pasted the cells contents into Notepad, the returns were gone, but if I did it to Word, they were there. Strange.

However, by using the ASC() Function at the point in the string where I knew there was SOMETHING, it returned a “10”.

Dr Google informed me that Chr(10) in Excel is the equivalent to Chr(13) & Chr(10) in Access.

From there I was able to use the Replace() Function in a simple query:
Replace([FieldName], Chr(10), Chr(13) & Chr(10))
...and now the text was formatted correctly.

Such a simple solution, it is embarrassing to think about how many “solutions” I tried before I stumbled across the answer.

Hope my mistakes can help someone else get there quicker than I did.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:39
Joined
Jul 9, 2003
Messages
16,243
Such a simple solution, it is embarrassing to think about how many “solutions” I tried before I stumbled across the answer.

It's exactly the same for me, I start creating something, something I think will only take a few minutes, maybe an hour, it ends up taking half a day, 3 days, sometimes a week. And I end up with what? 20 lines of code that a casual Observer would look at and say "that looks easy". People don't see all of the wrong directions you took, all of the head scratching, all of the searching the internet for a hints & tips.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:39
Joined
Sep 21, 2011
Messages
14,038
I created a small commision system in my last place of work. Took me quite a while at my level and was assisted with help from this forum. Documented it and had to show a colleague how to use it whilst I was on holiday.

His comment was 'Well it seems easy enough, quite simple really'
My response was 'You do not know how much work it was to make it appear that way.':D
 

Users who are viewing this thread

Top Bottom