Replacing ASCII Characters and other characters in a table 2

[kevmi98]

Hi,
I need desperate help with vba coding 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

 

[JoeMiller]

The box is Ascii code 10, a line feed. You can replace it by placing this function in a module:

[tt]
Function fstrTran(ByVal sInString As String, _
sFindString As String, _
sReplaceString As String) As String
Dim iSpot As Integer, iCtr As Integer
Dim iCount As Integer

iCount = Len(sInString)
For iCtr = 1 To iCount
iSpot = InStr(1, sInString, sFindString)
If iSpot > 0 Then
sInString = Left(sInString, iSpot - 1) & _
sReplaceString & _
Mid(sInString, iSpot + Len(sFindString))
Else
Exit For
End If
Next
fstrTran = sInString

End Function
[/tt]

Then you can call the function in an update query perhaps? The function takes a string, finds a character(s) in it, and replaces it with your character of choice. So here's how it would work to replace the box.

fstrTran([FieldName],Chr(10),Chr(13)+Chr(10))

That would find the box and replace it with a carriage return and line feed ("Chr(13)+Chr(10)&quot. To replace the ; with 5 spaces you would do this:

fstrTran([FieldName],";"," &quot

Hope that helps!

Joe Miller
joe.miller@flotech.net

 

[kevmi98]

I put it into a module and now I can't get the update query to find it. It is telling me undefined function.

 

[JoeMiller]

I've had this happen to me before when I paste a module in, I then try making a new database and it then finds it. Don't know why it does that, try it yourself see if it helps.

Joe Miller
joe.miller@flotech.net