General discussion

Locked

carraige returns in ms access text field

By mandajoy ·
how can i quickly find hard carraige returns in an access text field.. these hard carraige returns came from users copying multiple cells in excel and pasting them in one cell in access. this data now needs to be comma delimited and the returns arecausing big problems.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

carraige returns in ms access text field

by shmaltz In reply to carraige returns in ms ac ...

Use VBA.
Email me if you need more info.

Collapse -

carraige returns in ms access text field

by mandajoy In reply to carraige returns in ms ac ...

The question was auto-closed by TechRepublic

Collapse -

carraige returns in ms access text field

by parier In reply to carraige returns in ms ac ...

You want to try [NAME] & chr(10) & [address] & etc.

Try [NAME] & chr(10) & chr(13) & [address] & etc. if necessary.

Eric

Collapse -

carraige returns in ms access text field

by mandajoy In reply to carraige returns in ms ac ...

The question was auto-closed by TechRepublic

Collapse -

carraige returns in ms access text field

by TheStudent In reply to carraige returns in ms ac ...

I have run into this type of problem before and I don't know a fast way to do it in Access; however, I can tell you my "down and dirty" way to get the job done....

I export the files to Word and do a Find and Replace for the Special Character "Paragraph Mark". Then, import it back in.

Good luck !

Collapse -

carraige returns in ms access text field

by mandajoy In reply to carraige returns in ms ac ...

The question was auto-closed by TechRepublic

Collapse -

carraige returns in ms access text field

by SanKulPune In reply to carraige returns in ms ac ...

Hello mandajoy,

I tried hard but was unable to find a manual method to replace 'enter' with commas.

Depending on the method of import, the text may contain not only 'enter' but may also contain 'carriage return'.

So, here is VBA code that takes care of both.

Dim lStr As Integer
' Module level variable.

Public Sub FindEnter()
' SanganakSakha on 21/05/01
' Replaces all 'Enters & CRs' in a text field by commas
Dim rs As DAO.Recordset
Dim fld As Object
Dim str As String
Set rs= CurrentDb.OpenRecordset("Table1")
rs.MoveFirst
While rs.EOF <> True
For Each fld In rs.Fields
If (fld.Type = 10) And (IsNull(fld) = False) Then
str = fld
lStr = InStr(1, str, Chr(13))
If lStr > 0 Then
str = SubStr(str, Chr(13), ", ", lStr)
lStr = InStr(1, str, Chr(10))
If lStr > 0 Then
str = SubStr(str, Chr(10), ", ", lStr)
lStr = InStr(1, str, ", ,")
If lStr > 0 Then
str = SubStr(str, ", , ", ", ", lStr)
End If
End If
rs.Edit
fld = str
rs.Update
End If
End If
Next
rs.MoveNext
Wend
Set rs = Nothing
End Sub
'-------
Public Function SubStr(str1 As String, fstr As String, rstr As String, l As Integer) As String
If l > 0 Then
str1 = Left(str1, l - 1) & rstr & Right(str1, Len(str1) - l - Len(fstr) + 1)
SubStr str1, fstr, rstr, InStr(l, str1, fstr)
End If
SubStr = str1
End Function

I have successfully tried this code and would be happy to help you implement it.

Your feedback would be highly appreciated.

With best personal regards,

SanganakSakha
Your Friend In Need

Collapse -

carraige returns in ms access text field

by mandajoy In reply to carraige returns in ms ac ...

The question was auto-closed by TechRepublic

Collapse -

carraige returns in ms access text field

by mandajoy In reply to carraige returns in ms ac ...

This question was auto closed due to inactivity

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums