Access not importing line breaks in Memo Field correctly

[NumbersMan]

I am importing a Front Page comma delimited form results file into a
Microsoft Access database. Line Breaks are not being imported correctly from
the Front Page Textbox type field into an Access Memo Field. So I'm having to
go into Access and and insert line breaks by hand where they need to appear.
I suspect people are doing a cut and paste from some type of word processor
when they are entering their information in the Front Page Form in this
textbox field.

Do you have any suggestions so that these line breaks come into Acess
properly. These are the field types I need to use in both programs.

 

[John Vinson]

I am importing a Front Page comma delimited form results file into a
Microsoft Access database. Line Breaks are not being imported correctly from
the Front Page Textbox type field into an Access Memo Field. So I'm having to
go into Access and and insert line breaks by hand where they need to appear.
I suspect people are doing a cut and paste from some type of word processor
when they are entering their information in the Front Page Form in this
textbox field.

Do you have any suggestions so that these line breaks come into Acess
properly. These are the field types I need to use in both programs.

What shows in the memo field where you would expect the line breaks?
Nothing, a box, some other text? Some programs use just a linefeed
character (ASCII value 10); Access needs a carriage return followed by
a linefeed, Chr(13)&Chr(10). If your file contains just the linefeed
you can run an Update query using Replace() to correct it.

Note that text which is word-wrapped in another program probably will
not have actual linebreak characters (except at paragraph ends). It is
quite possible that the text coming in simply does not HAVE any new
lines defined, and I can't see how Access could figure out where you
want them to appear!

John W. Vinson[MVP]

 

[NumbersMan]

John,

Thanks. A box appears where a line break should be. bare in mind I'm not a
programmer. The find/replace funtion of Access does not allow me to find a
box (as it's not on my keyboard as a key). So given that how would I do a
search/replace?

Oddly enough if I simply copy/paste the text from Access memo field to a
word document then copy/paste it back (withouth having done anything) it
appears correctly in Access. This is not a fast work around however.

Thanks
Jay

 

[John Vinson]

Thanks. A box appears where a line break should be. bare in mind I'm not a
programmer. The find/replace funtion of Access does not allow me to find a
box (as it's not on my keyboard as a key). So given that how would I do a
search/replace?

An Update query will do it, but on thinking about it, it may be a bit
tricky: you probably have records with just a <linefeed>, but they may
be mixed in with records containing <carriage return><linefeed>!

Try this: run an Update query updating the memo field to

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

using a criterion of

LIKE "*" & Chr(10) & "*"

to replace all linefeeds with a CrLf pair; then run another query
updating the memo field to

Replace([memofield], Chr(13) & Chr(13), Chr(13))

with a criterion of

LIKE "*" & Chr(13) & Chr(13) & "*"

to remove the extra carriage returns which have been introduced.

John W. Vinson[MVP]

 

[NumbersMan]

I'll give this a try. However, I was hoping there was a switch I could switch
as I am running this type of import daily. I know I could automate something
but I'm not an access programmer. Is there any way this import problem can be
communicated to Mircosoft. As I mentioned, I am using Front Page in standard
configuration and this is a standard import routine into Access. I would
think they would want their products to be compatible with one another out of
the box for people like me.

Thanks
Jay