taking line breaks out

[dimitri]

i have a database of several thouseand entries. one column contains
keywords. some of these entries have a linebreak in the end. so what
happens is that the last word is not found in a query becouse of that.
if i take the linebreak out it works.

so is there any way in access, to take all the line breaks at once?

i am not confident with programming. the database is also linked to a
mysql database on a linux machine, in case that matters.

thanks, dimitri

 

[Larry Linson]

i have a database of several thouseand entries. one column contains
keywords. some of these entries have a linebreak in the end. so what
happens is that the last word is not found in a query becouse of that.
if i take the linebreak out it works.

so is there any way in access, to take all the line breaks at once?

i am not confident with programming. the database is also linked to a
mysql database on a linux machine, in case that matters.

Does that "also" mean there is a MySQL database "in addition to the Jet
database in which these entries with the linebreak exist" or does it mean
the entries with the linebreak are in the MySQL database?

Is the "last word" before or after the linebreak? If "after," could it be
that it is just on a separate line, and thus, not visible when you look at
the results of the Query?

Do you know the ASCII code of those linebreaks? You may be able to replace
them with a "null string" using the Replace function, in an Update Query. In
Access, the standard "new line" is Chr$(13) & Chr$(10) but that's not
universally the case in the world of Windows, and particularly not in the
world of Unix and its derivatives.

Larry Linson
Microsoft Access MVP

 

[dimitri]

the database is MySql on a Linux server. On winXP, Access i just link
to it.

The last word is before the linebreak. Access displays me a little
square at the end of those entries. however, i can't select it, but
when i click in the next line and delete manually the line break, it's
gone.

I was hoping that Access has somehow a function that you can take all
linebreaks out at once. otherwise i may post this on a MySql Linux
group, and see if they can help me.

Thanks, Dimitri

 

[Douglas J. Steele]

The line break is probably just the line feed character (Chr(10)), while
Access requires both a carriage return and a line feed (Chr(13) & Chr(10),
in that order).

To remove, try running an Update query:

Update MyTable Set MyField = Replace(MyField, Chr(10), "")

To change to something Access will recognize, try:

Update MyTable Set MyField = Replace(MyField, Chr(10), Chr(13) & Chr(10))

(This assumes you're using Access 2000 or newer: the Replace function
doesn't exist in Access 97 or earlier)

Note that you may want to change the line feed to something other than just
a zero-length string: you might want to use a space (" ") or some other
character (",", ":', etc.)

 

[dimitri]

thanks, i try that tonight when i get home from work.

never done a query in access before, but i'll try

cheers, dimitri