Return value from Function 1

[MrsMope]

Hi All,
I have a small function that evaluates a string looking for an apostrophe, I call this function from within a do while loop on a recordset that is inserting into a table. How do I get the result of my function ( string without "'") back?

here is my code:

[b][blue]Do while loop[/blue][/b]
Public Function CreateOutput(pstrSource As String, OutputType As Integer, Optional intPage As Integer)
Dim rstOutput As New ADODB.Recordset
Dim strSQL As String
Set cnnICInquiry = CurrentProject.Connection
Set rstOutput = New ADODB.Recordset
    rstOutput.Open StrSQLUnion, cnnICInquiry, adOpenKeyset, adLockOptimistic
    
    If rstOutput.EOF = True Then
    Else
    rstOutput.MoveFirst
    Do While rstOutput.EOF = False
        Call ReplaceEmbedded("'", " ", rstOutput!custname)

[b][red]The function [/red][/b]
Public Function ReplaceEmbedded(CharsToRemove As String, CharsToReplace As String, FromString As String)
'''------------------------------------------------
''' Replaces one character with another in a string
'''------------------------------------------------
        Dim i As Integer, s As String
        
        For i = 1 To Len(FromString)
            If InStr(CharsToRemove, Mid$(FromString, i, 1)) = 0 Then
                s = s & Mid$(FromString, i, 1)
            Else
                s = s & CharsToReplace
            End If
        Next i
        ReplaceEmbedded = s
End Function

I want the result of this function to insert into my table...any suggestions?

 

[Golom]

Using Call on a function just throws the returned value away. You need to assign the returned string to a variable.

Dim NewName as String
NewName = ReplaceEmbedded("'", " ", rstOutput!custname)

BTW - There is a VBA function called Replace that does this if you are using Access 2K or later.

 

[MrsMope]

Golom,
Thanks, I knew it was simple.

 

[PHV]

Anyway, if ac2k or above, a more efficient way:

Do While rstOutput.EOF = False
    NewName = Replace(rstOutput!custname, "'", " ")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886