DBVLOOKUP and MS Access text

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Hello All
I have searched the board for an answer to this question with no success.
Can anyone comment

Here's the function that has been previously posted:

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:\Program Files\foodmart 2000.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 & ";"
' 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

A person would use this formula (=DBVLookUp("account","account_id",A2,"account_description") in Excel to achieve a vlookup feature that uses Access to obtain the data.

First of all Thanks for posting this cool lookup function, but I can't get it to work when my lookup column field in Access is set to data type text. Is there another way?

Thanks in advance
Romefucan
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
I would think that you need to add some quotes to the line below:
Code:
" WHERE " & LookUpFieldName & "=" & LookupValue & ";"
new code:
Code:
" WHERE [" & LookUpFieldName & "] = '" & LookupValue & "' ;"

EDIT: I would also recommend adding brackets so that is does not fail when you pass in a filedname that contains spaces...

hth,
Giacomo
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Giacomo
Tried your ideas, still getting #VALUE error.
Something about text I guess, Am told the code works with just numbers but I have not verified this.

Romefucan
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
The code you posted works for me with numbers only, although text returns the #Value error. When I make the changes for text it works, but not for numbers. That can be worked around... but first can you try some different scenarios? like text vs. numbers and hard-coded values rather than cell refs and see if any of those work?

Also what is the value of A2? and what are the field types of the fields you're searching/returning?
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

As an alternative you may also want to try this function, I am using the DLOOKUP function in Access to get the value instead of opening an ADO connection. Make sure to reference the Microsoft Access 9.0 Object Library
Code:
Public Function DBVLookUp2(TableName As String, _
Criteria As String, _
ReturnField As String) As Variant

'Make sure to reference the Microsoft Access 9.0 Object Library
Dim ObjAccess As Object
Const DatabasePath As String = "C:\Program Files\foodmart 2000.mdb" 

On Error GoTo err_DBVLookUp2

'Open Access Database
Set ObjAccess = New Access.Application
ObjAccess.OpenCurrentDatabase DatabasePath

DBVLookUp2 = ObjAccess.Application.DLookup(ReturnField, TableName, Criteria)

'Housekeeping
ObjAccess.CloseCurrentDatabase
ObjAccess.Quit
Set ObjAccess = Nothing
Exit Function

err_DBVLookUp2:
MsgBox Err.Description, vbCritical, Err.Number
Resume Next
End Function

hth,
Giacomo
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Giacomo
A2 values are alphanumeric acronyms for example as a worse case sceanrio (L30_LMS.SWI)

These are control code acronyms in column 1 in Access. Column 2 is simply the alphanumeric description of the acronym. Both columns are text format in Access. There are currently over 160,000 acronyms

What I have in Excel is

Row 1 is nothing but acronyms cut and pasted in, Row 1 can be 200 columns wide

Row 2 is where I want Excel to go lookup the acronyms in row 1 in the database and return the correct descriptions to row 2 under the correct acronym.

Sounds simple right.

Romefucan
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60

ADVERTISEMENT

Text Code

Giacomo
Can you post the code that works for text. I may be able to use it?
Thanks in advance.
Romefucan
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
it's the same as yours with that one little change I posted.
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
Code

Giacomo
Here is the code exactly as written with your changes. Still giving me the #VALUE error when looking for text.

Cell B2=DBVLookUp("Device Signals","Device Signal",B1,"Description")
Using Cell B1 as the lookup= AAT

Code
Const DatabasePath As String = "D:\Visual Engineering Tools\Acronyms.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 " & "Device Signal" & "," & "Description" & _
" FROM " & "Device Signals" & _
" WHERE [" & "Device Signal" & "] = '" & 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(Description).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
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
dont put quotes around your variables. If you do they will be treated as literals.

Code:
Const DatabasePath As String = "D:\Visual Engineering Tools\Acronyms.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 " & Device Signal & "," & Description & _ 
" FROM " & Device Signals & _ 
" WHERE [" & Device Signal & "] = '" & 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(Description).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

BTW - did you try my alternate function?

hth,
Giacomo
 

Forum statistics

Threads
1,141,847
Messages
5,708,950
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top