VBA code to replace chr10 and chr13 to space

[reedhillltd]

Hello I'm working on an eBay uploader access program for Ebay's File Manager

Our description contains line feeds and carriage returns. I have very little knowledge of Vba, and I can t find any tutorials on how to replace the characters with spaces on how to program this.

The table is called tblCustomer
The field is called *Description

Thank you in advance

I'm using access 2007

 

[BlueIshDan]

Run a query

UPDATE tblCustomer Set description = Replace(description, Chr(13), " ")

Something like that

 

[reedhillltd]

thankyou for the reply

in need to replace both chr10 and chr13 at the same time

is there any way to run two replace functions within one field

 

[BlueIshDan]

run two queries replace the 13 with 10 in the second.

 

[reedhillltd]

is there any way I can make a button in my form that when clicked will run both queries automatically

 

[BlueIshDan]

Try that out. lol

UPDATE tblCustomer 
	Set description = 
                IIF(
			InStr(1, description, Chr(13),
			Replace(description, Chr(13), " "),
			IIF(
				InStr(1, description, Chr(10)),
				Replace(description, Chr(10), " "),
				description
                           )
 		   )

 

[BlueIshDan]

This is all air code, so let me know if you get any errors.

Private Sub btnButton_Click()
    CurrentDb.Execute "UPDATE tblCustomer " & _
                            "Set description = " & _
                                "IIF(" & _
                                    "InStr(1, description, Chr(13)) > 0," & _
                                    "Replace(description, Chr(13), "" "")," & _
                                    "IIF(" & _
                                            "InStr(1, description, Chr(10)) > 0," & _
                                            "Replace(description, Chr(10), "" "")," & _
                                            "Description" & _
                                        ")" & _
                                ")"
End Sub

 

[pr2-eugin]

Why so many InStr? You only need.

UPDATE tblCustomer SET description = Replace(Replace(description, Chr(10), ' '), Chr(13), ' ')

Give the above Query a name (say replaceQry), then use in the button code.

Private Sub buttonName_Click()
    CurrentDB.Execute "replaceQry"
End sub

 

[BlueIshDan]

LMFAO don't mind me.
Not a coding day for this mind.

 

[pr2-eugin]

LMFAO don't mind me.
Not a coding day for this mind.

We all have one of those days !