VBA Lookup Function to Access Returning #value

[troygeri]

Hi - new to the forum so not sure if this si the right area for this question

Hi

I am using the following lookup function but it keeps returning a #value result? I am then using the following in the cell where i want the value returned

=DBVLookUp("004-MINF Mapping to ISO","NAB_XML_FIELD_PATH",B5,"FIELD_LOGICAL_ID")



Where 004-MINF Mapping to ISO is the table in the Access DB, NAB_XML_FIELD_PATH is the column in the table where I am searching for the value contained in B5 and FIELD_LOGICAL_ID is the value I want returned

I have put this code into a MODULE and set all the Microsoft ActiveX settings on, can't understand why I am getting a result of #value as the Cell value in B5 gets an exact match in the table column I am looking up?

I am using Excel 2010 and Access 2003

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 Long, _
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 & "';"
' If lookup value is a number then remove the two '
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" 'Change to 3.51 for Access 97
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

[rpeare]

Hi - new to the forum so not sure if this si the right area for this question

Hi

I am using the following lookup function but it keeps returning a #value result? I am then using the following in the cell where i want the value returned

=DBVLookUp("004-MINF Mapping to ISO","NAB_XML_FIELD_PATH",B5,"FIELD_LOGICAL_ID")

Where 004-MINF Mapping to ISO is the table in the Access DB, NAB_XML_FIELD_PATH is the column in the table where I am searching for the value contained in B5 and FIELD_LOGICAL_ID is the value I want returned

I have put this code into a MODULE and set all the Microsoft ActiveX settings on, can't understand why I am getting a result of #value as the Cell value in B5 gets an exact match in the table column I am looking up?

I am using Excel 2010 and Access 2003

I do not know about excel functions but if DBVlookup is a function in excel that looks up items from a ms access database I would look at the syntax of your lookup, in access the syntax is:

dlookup("[Field]", "Table", "[SearchField] = <criteria>")

So for you it would be


dlookup("[FIELD_LOGICAL_ID]", "004-MINF Mapping to ISO", "[NAB_XML_FIELD_PATH] = " & B5)


Because your table has spaces in it you may also need to enclose it in square brackets ([004-MINF Mapping to ISO]), remember spaces and special characters in object names leads to a lot of aggravation.

assuming B5 is a numeric value

if it's a text value you'd have:

dlookup("[FIELD_LOGICAL_ID]", "004-MINF Mapping to ISO", "[NAB_XML_FIELD_PATH] = '" & B5 & "'")

just check to make sure you're listing things in the appropriate order for the dbvlookup command.

[June7]

Yes, must have [] for the table name:

DLookup("[FIELD_LOGICAL_ID]", "[004-MINF Mapping to ISO]", "[NAB_XML_FIELD_PATH] = '" & [B5] & "'")