search/replace Enter key press within field

F

FranTic64

I have an Access 2002 table that has a Customer name field in which some
users have pressed "Enter" to create a second line in the customer name. Now
we are converting to a FoxPro front end, with SQL tables. When the data
displays in FoxPro the "Enter" displays as a rectangle and interferes with
certain functions. When I view the SQL table using Access, it still shows
the Customer name as two lines. How can I search for and replace, or update
the data to eliminate this "Enter" key character?
 
J

John Spencer (MVP)

You can use the Replace function in an update query to get rid of the
characters. Check online help for the syntax for the replace function as I am
going from memory.

UPDATE YourTableName
SET CustomerName = Replace([CustomerName],Chr(13) & Chr(10),"",1,-1,1)
WHERE CustomerName Like "*" & Chr(13) & Chr(10) & "*"
 
Top