Convert Multi Line to Single Line

[tristap]

Can anyone advise if there is a way to import an MS Access table which has a multi line comment field into a single lined field ie. get rid of the carriage returns on import?

 

[SuryaF]

You can import it in the regular way and then apply an update query like:

UPDATE [MyTable] SET [MyTable].[MyField] = Replace(nz([MyTable].[MyField],""),Chr(13) & Chr(10),"");

You can also create a procedure that applies this transformation for any combination of Table & Field.

Sub RemoveCrLf(TableName, FieldName)
CurrentDb.Execute "UPDATE [" & TableName & "] SET [" & TableName & "].[" & FieldName & "] = Replace(nz([" & TableName & "].[" & FieldName & "],''),Chr(13) & Chr(10),'');"
End Sub

 

[tristap]

Can you please explain the above code and also why I would need to pass the table and field name to the procedure. Would I not just include it in the sql statement?

 

[tristap]

Added a button on a form to execute your code supplied and the below works:

Private Sub cmdReplaceText_Click()
Call RemoveCrLf("zz", "txtDescription")
End Sub

Sub RemoveCrLf(TableName, FieldName)
Dim mySQL As String
mySQL = "UPDATE [" & TableName & "] SET [" & TableName & "].[" & FieldName & "] = Replace(nz([" & TableName & "].[" & FieldName & "],''),Chr(13) & Chr(10),'');"
CurrentDb.Execute mySQL
End Sub

Thanks for the help!