Hard Return Ascii Symbol (box) in Field

[Tracey_Gigatta]

I have an address field and in the form's property of "Enter Key Behavior", I
have "New Line in Field" selected. I have this same field multiple times for
different types of contacts, and all are set up the same way (i.e., technical
contact, executive contact, billing contact, etc). All of a sudden, in the
tech contact address field where there are hard returns, there is now a box
(ascii symbol for carriage return?) instead of a new line. The other address
fields have not changed. Why is this changing, but most importantly how can
I change this back to display a new line and remove the boxes? FYI -- since
I do not know code, I need a fairly simplified answer. All of my design has
been done via gui's/menus, etc. Thanks!

 

[Douglas J. Steele]

Access is pickier than some other applications.

To have a new line, you must have a Line Feed (which is Ascii 13) AND a
Carriage Return (Ascii 10), in that order.

If you, say, copied and pasted from some application that only requires a
Line Feed or a Carriage Return, you'll get the behaviour you're describing.

 

[Tracey_Gigatta]

Thank you, but is there a way I can fix the data in this field? I recall how
this happened. I had an earlier issue where I had corrupt records. In
fixing that, I had exported data into excel, then imported back into Access.
When doing so, it did exactly what you said below.

 

[John Spencer]

Try using REPLACE in an update query.

UPDATE [yourTable]
SET [Your field Name] = Replace([Your field Name], Chr(10),Chr(13) & Chr(10))
WHERE [Your field Name] Like "*" & Chr(10) & "*"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County