Delete hard return

[rHartman]

I imported a excel file with a cell that has alot of text
and hard returns to make new/next lines. When it comes
into access it in one long line of text, with a thick hard
line showing as the hard return. How can I remove the
thick line and replace with a enter.

 

[Douglas J. Steele]

Odds are that the Excel data has only a carriage return (Chr$(13)), whereas
Access needs both a carriage return and a line feed (Chr$(10)) in that
order.

Assuming you're using Access 2000 or newer, try using the Replace function
to change the carriage returns to carriage returns and line feeds:

Replace(YourTextVariable, Chr$(13), Chr$(13) & Chr$(10))

If that doesn't work, maybe the Excel has a line feed, not a carriage
return, so try

Replace(YourTextVariable, Chr$(10), Chr$(13) & Chr$(10))

 

[Ken Snell]

EXCEL uses Chr(10) to start a new line in a cell.

 

[Douglas J. Steele]

Thanks, Ken. I was too lazy to check. <g>

 

[Douglas J. Steele]

In response to private e-mail:

I have access97

i tried this in Access 2000 and it deletes all the data.
I try it in the update field of the query not VBA. What
is the CHR codes for Access 97 enter and return.

Carriage Return and Line Feed are the same in Access 97 as in every other
version of Access: it's an Ascii thing, not an Access thing.

If you're using Access 97, you'll have to write your Replace-equivalent
function. One such function is at
http://www.mvps.org/access/strings/str0004.htm at "The Access Web".

However, if you're going to be calling that from a query, and there's a
chance that some of the fields may be null, you'll need to make a little
tweak:

Function FindAndReplace(ByVal strInString As Variant, _
strFindString As String, _
strReplaceString As String) As String

Dim intPtr As Integer
Dim strWorking As String

strWorking = strInString & vbNullString
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strWorking, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & left(strWorking, intPtr -
1) & _
strReplaceString
strWorking = Mid(strWorking, intPtr +
Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strWorking
End Function

There's no reason it would have deleted your values in Access 2000 unless
you miskeyed something.

 

[robin]

Thank you

-----Original Message-----
In response to private e-mail:


Carriage Return and Line Feed are the same in Access 97 as in every other
version of Access: it's an Ascii thing, not an Access thing.

If you're using Access 97, you'll have to write your Replace-equivalent
function. One such function is at
http://www.mvps.org/access/strings/str0004.htm at "The Access Web".

However, if you're going to be calling that from a query, and there's a
chance that some of the fields may be null, you'll need to make a little
tweak:

Function FindAndReplace(ByVal strInString As Variant, _
strFindString As String, _
strReplaceString As String) As String

Dim intPtr As Integer
Dim strWorking As String

strWorking = strInString & vbNullString
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strWorking, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & left (strWorking, intPtr -
1) & _
strReplaceString
strWorking = Mid(strWorking, intPtr +
Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strWorking
End Function

There's no reason it would have deleted your values in Access 2000 unless
you miskeyed something.

--
Doug Steele, Microsoft Access MVP



(Chr$(13)),


.