Lookup data in Oracle database

[SQUASH JUNKI]

Hi
I am trying to lookup data in an oracle table based on a value in my excel spreadsheet. I am using the DBVLOOKUP function that I found on Google, and am trying to convert the connection string to use an ORACLE connection.
The code I have is as follows...

Dim adoCN As ADODB.Connection
Dim strSQL As String
 
Public Function DBVLookUp(TableName As String, _
                          LookUpFieldName As String, _
                          LookupValue As String, _
                          ReturnField As String) As Variant
    Dim adoRS As ADODB.Recordset
    Set adoCN = New Connection
    adoCN.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=myTNSname;User Id=myuser;Password=mypass;"
    adoCN.Open
 
    Set adoRS = New ADODB.Recordset
    strSQL = "SELECT DISTINCT" & LookUpFieldName & ", " & ReturnField & _
             " FROM " & TableName & _
             " WHERE " & LookUpFieldName & "=" & LookupValue & ";"
 
    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
    If adoRS.BOF And adoRS.EOF Then
        DBVLookUp = "Value not Found"
    Else
        DBVLookUp = adoRS.Fields(ReturnField).Value
    End If
    adoRS.Close
End Function

The module does not show any errors, the SQL statement is correct, and it does seem to be connecting to the data. However I only get #value showing in my spreadsheet.
The function on the spreadsheet looks like..

=DBVLOOKUP("TableName","LookUpFieldName", $F$23,"ReturnField")

where $F$23 is the cell that is being referenced for lookup in the Oracle DB.

I cant see what's going wrong , if someone could point me in the right direction that would be a great help.

Thx

[Bob Phillips]

Surely, the column in the db that you are looking up is not really called ReturnField?

[SQUASH JUNKI]

errr...no.
The VB code (Public Function DBVLookUp....) is to create a function "DBVLookup" which you can in turn use within the worksheet.

[Bob Phillips]

Yeah but it gets the values in a column that you supply via the ReturnField value, so passing the value ReturnField seesm wrong to me, I would expect something likeName.

Ditto Tablename.

[SQUASH JUNKI]

When the function is used in the spreadsheet itself, the format is

=DBVLOOKUP("TableName","LookUpFieldName", $F$23,"ReturnField")

but of course these are replaced by the actual table/field names when the function parameters are filled in.

=dbvlookup("dw.customer","sap_number_conversion",F23,"customer_name")