I currently have a database which I'm trying to connect Excel to to automatically fill in some work reports.
I need the UDF to take in 3 variables - two of which are for the WHERE section of the SQL statement to match the result, the third is for selecting which field/column is req'd. The database and tablename are hardcoded.
The database will only ever have one match for the two variables - i.e. only one ref with a particular date, so when I ask for a particular field to be returned there will only be a single answer (which could be a string, date, time or integer, depending on what I request).
It keeps returning #VALUE!
Public Function ANITEDataExtract(d As String, f As String, r As String) As String
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=ops-serv;INITIAL CATALOG=ANITEFigures;"
'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"
'Now open the connection.
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim sqlStr As String
sqlStr = "SELECT " & r & " FROM S04 WHERE Date='" & d & "' AND FltNum='" & f & "'"
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
' Copy the records into cell A1 on Sheet1.
ANITEDataExtract = Str(rsPubs)
' Tidy up
Set rsPubs = Nothing
Set cnPubs = Nothing
Can anyone please help me solve this problem?