Dealing with carriage returns (1 Viewer)

[Les Isaacs]

Hi All

I have a routine that imports data from an excel file into an access table and then generates a report based on the table. One of the cells in the excel file contains free text, typically a few sentences. In some cases the sentences are separated onto new lines - the user has done alt-return to generate the new line. When I import the cell into a memo field in my access table, if I view the record directly (by opening the table) the 'new line' shows as a little square with a question mark in it. The report also shows the shows the little square with a question mark in it - rather than treating it as a carriage return and putting the next sentence on a new line.

How can I replace the little squares with vbNewLine, or something, that will be recocnised by access?

Hope someone can help.
Many thanks
Les

 

[AccessMSSQL]

Use Replace([YourField],chr(10),"")

Actually, the above will replace the line feed with nothing.

You can try Replace([YourField],chr(10),chr(10) & chr(13))

 

[Les Isaacs]

Hello 'AccessMSSQL'

Many thanks for your reply. On trying your suggestion I found that only where two carriage returns had been entered (so two little squares) was I getting a new line generated. I therefore added two further Chr(10) & Chr(13)s to the replace function:

Replace([NotesText],Chr(10),Chr(10) & Chr(13) & Chr(10) & Chr(13))

... and this does the trick.

Slightly irritatingly, I do still see the little squares in the memo field of the table (and in the report), but the main thing is that the sentences are separated onto new lines. Is there a way to prevent the little square from being visible?

Thanks again for your help.
Les

 

[AccessMSSQL]

sorry been off the forums for about a week. Did you figure out how to get rid of the squares?

 

[Les Isaacs]

No:banghead:

 

[AccessMSSQL]

yikes...! The little squares are probably a different character. What I would do is write a little function and return the ascii code for each character in a particular comment field then you can figure out what character it actually is and remove it.

Function GetAsciiCode(sData)
Dim iLen as integer
Dim i as integer
Dim s as string
iLen = len(sData)
for i = 1 to iLen - 1
s = mid(sData,i,1)
debug.print Asc(s)
next

end function

You can put a breakpoint in there and just call the function from immediate window. Paste your memo field text into the function.

 

[AccessMSSQL]

I may have steered you wrong a bit. Try switching the replacement to: Chr(13) & Chr(10), not the other way around. My bad. Sorry about that. Try using that in the original formula you had before you added the extras. I did a quick test and Chr(10) & Chr(13) produces two rows with a blank space. Chr(13) & chr(10) produces just one carriage return and takes you to beginning of next line.

 

[Les Isaacs]

Hello 'AccessMSSQL'

That's it - swapping the order of Chr(13) & Chr(10) did the trick perfectly

Very many thanks for your help.
Les