Replacing ASCII Characters in a table

[kevmi98]

Hi,
I need desperate help with this problem that I am trying to do. I have records in a table that all look like the following:
Record 1
John Smith;SomeCompany;3552110unknownasciicharacterAnna Smith;SomeCompany;2222212unknownasciicharacter

Record 2:
JohnDoe;SomeCompany;2222222unknownasciicharacterJaneDoe;SomeCompany;1111111

The unknownasciicharacter appears as a box. What I need is to replace that with a carriage return on all fields. I also need to replace the ; with spaces. I have tried working with it on a form and a report and I can't seem to get anything to work.

Susan
SusanS@genwest.com

 

[DougP]

VBA spoken here #-V

you need to create a function
It is going to take a while to perfect it but
keep at it and it will get you there.

here is a start:
Go in the modiles and paste this code in there
Then press ctrl-G and type in CleanUpRecords and press Enter
---------------------------------------
Public Function CleanUpRecords()
Dim db As Database, rst As Recordset, SQL As String
Dim GetSquare, GetSomething As Integer
Set db = CurrentDb
' SQL string.
SQL = "SELECT * FROM [YourTablename];"
Set rst = db.OpenRecordset(SQL)
rst.MoveLast
rst.MoveFirst

Do Until rst.EOF
' this replaces the first "Square" with a Carriage return line feed
GetSquare = InStr(1, rst!FieldName, &quot;?&quot '< -----copy and paste the square in here
Mid(rst!FieldName, GetSquare, 1) = Chr$(10) & Chr$(13)
' anythign else you want to replace add another statement like above
' putting it in side double quotes
GetSquare = InStr(1, rst!FieldName, &quot;;&quot '< -----copy and paste the other character in here
Mid(rst!FieldName, GetSquare, 1) = &quot; &quot;
rst.MoveNext
Loop

rst.Close
db.Close

End Function

good luck

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tlbroadbent]

I suspect the box character may be chr(10) or chr(13).

You could create a VBA function to utilize the REPLACE function.

Function MyReplace(sString As String, sOld As String, sNew As String) As String
MyReplace = Replace(sString, sOld, sNew)
End Function

Then call MyReplace in an update query.

Update Table Set col1=MyReplace(Myreplace(MyReplace(col1, &quot;;&quot;, &quot; &quot, chr$(10), chr$(13) & chr$(13)), chr$(10), chr$(13) & chr$(10))
Where col1 Like &quot;*;*&quot;
Or col1 Like &quot;*&quot; & chr$(10) & &quot;*&quot;
Or col1 Like &quot;*&quot; & chr$(13) & &quot;*&quot;

This will change the &quot;;&quot; to space and convert chr$(10) and chr$(13) to a Carriage Return/Line feed in the same statement. If the box character is not chr$(10) or chr$(13) then substitute the value in the query.

I've tested this procedure in Access 2000. I'm not sure if it will work in earlier versions. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes