Replace character in fields on update query 2

[nja5150]

I was wondering if anyone could help me with some SQL code to run an update query for a memo field I have. The memo field was imported from a dbase file and as a result I have these funny characters throughout the memo field in each record. The problem is defined in MS Knowledge base and their solution is described below:

RESOLUTION
You can create an Access Basic procedure to remove these characters from the Memo field. The procedure should go through each record in the table and copy the Memo field, character by character, to a temporary holding area, ignoring all instances of Chr(161). The procedure should then copy the corrected string back into the Memo field.

An example of my memo field is below:
11/23/2001 CAR SOLDìON 10-24-2001ìCUSTOMER TO PICK UP.ì
#40545.

The "i" character is followed by a "|" looking character that is bold (this character doesn't come through when I paste here) These two characters need striped and replaced with a space.

Before someone tells me I'm posting in the wrong group...I was wondering what the SQL solution to this problem would be. I'm still awaiting help from some of the VB forums here and so far the solutions I've gotten haven't been very simple.

 

[dan1967]

I believe the function you are looking for is Replace(). The usage is:

Replace(ColumnName, Char(161), ' ')

"ColumnName" is the name of the column you want to scan, "Char(161)" is what you are scanning for, and "' '" is what you are replacing it with. You can also nest these to remove more than one character. For instance, if you want to remove all Tab stops and line feeds, use the following:

Replace(Replace(ColumnName, Char(9), ' '), Char(10), ' ')

 

[SonOfEmidec1100]

VB code is very similar, assuming the bold pipe character is a LF (most likely)

NewString = Replace(MemoString, Chr(161) & Chr(10), " &quot

 

[nja5150]

SonOfEmidec1100-

I realize this post is going outside the SQL forum topic but please bear with me. I'll post this in the VB forum if I have to.

How can I use the example above in a query as a new field I know how to creat the public funtion and name it and define the NewString and MemoString variables but I can't seem to make it usable as a field in a query. I think I could get it to work in a data set. I THINK.

I am extreme noob with code.

For example I am doing something like this:

SQL (Select Query)
-------------------
SELECT RemoveCRLF(REMARKS) AS NewField
FROM lne_rec;

VB MODULE
----------
Public Function RemoveCRLF(Value As String) As String
Dim i As Long
Dim TmpStr As String
Dim TmpChr As String

For i = 1 To Len(Value)
TmpChr = Mid(Value, i, 1)
If TmpChr = Chr(236) Then
TmpChr = ""
End If
TmpStr = TmpStr + TmpChr
Next
RemoveCRLF = TmpStr
End Function


This works--but if the field DOES NOT have these characters I get an ERROR# in the field. I think this has to do with an empty REMARK field and therefore there is no place for the Mid value to work. I also suspect your recommendation is alot more flexable and efficient

Thanks!

 

[SonOfEmidec1100]

As far as I can tell the routine should work except when REMARKS contains a NULL.

If thats the case then try something like

Public Function RemoveCRLF(Value As Variant) As String
Dim i As Long
Dim TmpStr As String
Dim TmpChr As String

If isnull(Value) then
TmpStr = ""
else
For
....
Next
end if
RemoveCRLF = TmpStr
End Function

 

[dan1967]

I want to thank you for the star, nja5150. I hope all goes well for you.

 

[nja5150]

Ok, I'll give that code a shot, and THANKS!!!!

Just for my curiousity how would I use the code you gave using the REPLACE method?

I must not be declaring something properly in the module, or something. I can't get it to work like the SQL and VB example above.

I really appreciate the help guys, I've been working on this off and on for 3 weeks!!!

feel free to email me if it's easier or more appropriate: nalongi@hotmail.com (I'll be sure to post any replys here to help others in the future)