NauticalGent
Ignore List Poster Boy
- Local time
- Today, 10:08
- Joined
- Apr 27, 2015
- Messages
- 6,339
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.
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.