Error 3085;Undefined Function Error

[rl78]

Hi All,
I am trying to run this code (in an Access XP module) that I copied off of the MS Support Site to convert the CR character used in Microsoft Excel to the CR character used in Microsoft Access (Knowledge Base Article 115576). I followed all of the directions, and even tried looking for help, but still get the error. I have no References listed as missing, please help if you can. Thanks.
-Code starts below-

Public Function ChangeStr(s As Variant, a As String, n As String, c As Integer) As Variant
' This function changes all substrings "a" and string "s"
' to "n." The parameter "c" has the same function as in the
' InStr() function.
Dim temp As String, pos As Integer
temp = ""
If IsNull(s) Then
ChangeStr = Null
Exit Function
End If
If a = "" Or s = "" Then
ChangeStr = s
Exit Function
End If
pos = InStr(1, s, a, c)
While pos > 0
temp = temp & Mid$(s, 1, pos - 1) & n
s = Right$(s, Len(s) - pos - Len(a) + 1)
pos = InStr(1, s, a, c)
Wend
ChangeStr = temp & s
End Function

 

[rl78]

Also, forgot to add, that the module compiles without any errors, however, the article explains to create an update query with that fieldname of the that I need to fix: Here is the update query they advised me to use: ChangeStr([<fieldname>],Chr$(10),Chr$(13) & Chr$(10),0)

 

[mikevh]

The function looks okay. I'm wondering about your
update query. What you refer to as an &quot;update query&quot;
in your post is only part of one -- it's just the
value to update to.

It would be difficult to duplicate the QBE grid
appearance here, but the SQL for the query would look
like this:

UPDATE YourTable
SET YourField=ChangeStr([YourField],Chr$(10),Chr$(13) & Chr$(10),0)

where YourTable and YourField represent your table
and the field you want to change.

 

[rl78]

Hi mikevh,
Yes, thats exactly what the update query looks like in SQL View, it is UPDATE Matter SET Matter.MatterComment = ChangeStr([MatterComment],Chr$(10),Chr$(13) & Chr$(10),0); Im pretty sure that the knowledgebase gave me the correct code, im just still baffled why this keeps giving me that error. Any suggestions on how I can achieve replacing the square boxes in the field with hard returns(i.e. line feeds). I pretty much need to have this Memo field break to a new line wherever the square boxes appear in the text.
Thanks.

 

[mikevh]

Well, I tried this myself, following the directions in
the Knowledge Base Article 115576, and it worked like a
charm for me. The only glitch was when I first pasted the
function into a module, there was no space before the line
continuation character after &quot;n as string&quot; in the function
header, so the header was highlighted in red. After I put
a space there, all went well. (You say the module compiled for you, so either you caught this too or it didn't happen
-- maybe it was something in the way I cut-and-pasted.) I followed the instructions in the article for creating and importing the Excel spreadsheet, imported it into Access, ran the update query, and it did indeed translate what looked like little square boxes into linefeeds. (When
I imported the spreadsheet into Access, it imported the
field as Text, but I changed it to Memo after the import
since you said your field is Memo.) As noted in the article, I did have to expand the row height in the table in order to see the new lines.

So, at this point, I'm kinda stumped. You say you're not
missing any references, but I wonder ... Here's what I
get when I test the Chr$ and ChangeStr functions in the
Immediate window. Is this what you get?

?chr(65)
A
?chr$(65)
A
?changestr(&quot;xyz&quot;,&quot;x&quot;,&quot;a&quot;,0)
ayz

 

[mikevh]

P.S. I'm using Access 97, and the only 3 references I
have checked are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library