HI
I have tried this function but only keep getting "Value not found" as my cell result even though the string value I am looking up is definitely in the table, column I have specified in the function - see code below
Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "
\\aur\hobo_data\corporate\corporate\3dock2\D_IMA\Teams\Data Architecture and Management\Projects\Payments Transformation\02 - Documents\23 - Data Architecture Deliverables\11 - ODS & Data Mart Model\04 Fundtech Data Provision\Payments Transformation.mdb"
'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return
Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection
Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & 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
Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub
