Need assistance converting Oracle function to Access Function

[adventurous1]

Hi...

I have an Oracle function that needs to be converted to an Access function (below). I have tried but cannot get it to work in Access. Can someone help out by posting the converted function?

Thanks!

create or replace function advent (f1 in varchar2, f2 in varchar2) return varchar2 is
hold_string varchar2(4000);
begin
for r in (select field3 from adventurous where f1 = field1 and f2 = field2) loop
if length(hold_string) > 0 then
hold_string := hold_string||', ';
end if;
hold_string := hold_string||r.field3;
end loop;
return hold_string;
end;

 

[MazeWorX]

Untested

Public Function advent(f1 As String, f2 As String)

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Dim holdString As String

    Set db = CurrentDb()
    strSQL = "SELECT field3 FROM adventurous " _
             & "WHERE (((adventurous.field1)=f1 AND ((adventurous.field2)=f2));"
    hold_string = ""
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        With rs
            If Len(hold_string) > O Then
                hold_string = hold_string & ", "
            End If
            hold_string = holdString & rs!field3
        End With
        rs.MoveNext
    Loop

    advent = hold_string

End Function

Thoughts?


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[adventurous1]

Thanks...I will try this and close if it is successful. Appreciate the assistance!

 

[MazeWorX]

sorry SQL correction should read

"SELECT adventurous.[/color blue]field3 FROM adventurous " _

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work