I work with access and excell 2003. If cell in excell contains data as follows: Mary Smith Long Beach John James Los Vegas Then upon import or linking spreadsheet into access, a little square pops in. This creates havoc in formatting, when I do a mailmerge between access and Word. I don't know programming, vba, sql or anything fancy. Using an update query for field [people] what is the syntax to lose the square and replace with appropriate carriage return and/or line fees. PS. I looked at an article at http://support.microsoft.com/kb/210372 but access wouldn't accept it.
A "hard return" in an EXCEL cell is the line feed character (Chr(10)). A "hard return" in an ACCESS field is the combination of the carriage return and line feed characters (Chr(13) & Chr(10)). The square box you see in the ACCESS field is the line feed character. What you need to do is run an update query on the table so that you convert the line feed character to the combination: UPDATE YourTableName SET YourFieldName = Replace(YourFieldName, Chr(10), Chr(13) & Chr(10)); Note that you want to run this update query ONLY ONCE on the table after you import the data. Otherwise, you'll end up with multiple,consecutuve Chr(13) characters in your data. -- Ken Snell <MS ACCESS MVP> "bird lover" wrote in message news:DF96B94C-7BAF-433D-97B9-2CB734374B47@microsoft.com... >I work with access and excell 2003. If cell in excell contains data as > follows: > Mary Smith > Long Beach > > John James > Los Vegas > > Then upon import or linking spreadsheet into access, a little square pops > in. This creates havoc in formatting, when I do a mailmerge between > access > and Word. > I don't know programming, vba, sql or anything fancy. > > Using an update query for field [people] what is the syntax to lose the > square and replace with appropriate carriage return and/or line fees. > > PS. I looked at an article at http://support.microsoft.com/kb/210372 but > access wouldn't accept it.
