DBVLOOKUP and MS Access text

[Romefucan]

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

 

[Romefucan]

Giacomo
I am getting a syntax error in the SQL statement, won't compile

strSQL = "SELECT " & Device Signal & "," & Description & _
" FROM " & Device Signals & _
" WHERE [" & Device Signal & "] = '" & LookupValue & "' ;"

No, I have not tried your alternate function, but its on my list for today

Romefucan

 

[VicRauch]

Is "Device Signal" the variable that holds the name of the lookup field? If so, it should be used with brackets "[ ]"around it in your code:
... & [Device Signals] & ...
Without the brackets, VBA thinks you are using two variables.
Is "Device Signals" the variable that holds the name of the table you are doing the lookup in? If so, again it needs the brackets around it because it also has a space in it's name.

 

[giacomo]

nice catch Vic, I completely missed that.

Giacomo

 

[Romefucan]

Variables/ Fields

Gentlemen
Device Signals with an "S" is the Table
Device Signal without the "S"is the field that is being looked up?

Hope this clarifys.

Romefucan

 

[VicRauch]

I noticed that, but what about putting the brackets "[ ]" around the variable name? It will not work without it, therefore you are getting a compile error.

 

[Romefucan]

Code

Gentlemen
Cell B2=DBVLookUp("Device Signals","Device Signal",B1,"Description")
Cell B1= AAT

Code
'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

Still getting #VALUE Error


However, after trying Giacamo's other approach posted above

Cell B2==DBVLookUp2("Device Signals",B1,"Description")
Cell B1 = AAT

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 = "D:\Visual Engineering Tools\GE Acronyms.mdb"

On Error GoTo err_DBVLookUp2

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

DBVLookUp2 = ObjAccess.Application.DLookup("Description", "Device Signals", Criteria)

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

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

Getting the Error : The Expression you entered as a query parameter produced this error: Microsoft Access cannot find the name you entered AAT in the expression.

Maybe there is hope for Giacomo's second approach?

 

[Romefucan]

A breakthru

Gentlemen
The code below is showing some progress thanks to Giacomo. In its present form the code is returning my acronym description however it is returning the first record description in every column

There must be a variable missing. can you please have a look? Thanks again for your patience.

Example of problem
Cell B2=DBVLookUp2("Description","Device Signals",B1)
Cell B1 = AAT
Code Returns to Cell B2 "AA" ("AA" is First record Description in Access)

Cell C2=DBVLookUp2("Description","Device Signals",C1)
Cell C1 = AFPAP
Code Returns to Cell C2 "AA" ("AA" is First record Description in Access)

And so on in every column

Code as follows:

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 = "D:\Visual Engineering Tools\GE Acronyms.mdb"

On Error GoTo err_DBVLookUp2

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

DBVLookUp2 = ObjAccess.Application.DLookup("[Description]", "Device Signals", [Device Signal])


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

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

 

[giacomo]

please give this a try...

Cell B2=DBVLookUp2("[Description]","[Device Signals]","[Device Signal] = '" & B1 & "' ")
Cell B1 = AAT

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 = "D:\Visual Engineering Tools\GE Acronyms.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

EDIT: I have edited this post about a dozen times now... sorry to confuse

hth,
Giacomo

 

[Romefucan]

RE: A breakthru

Giacomo
When pasted into the cell I get an error "Name not Valid" it seems it can complete the code up to the first single quote.



Romefucan

 

[giacomo]

Re: RE: A breakthru

Giacomo
When pasted into the cell I get an error "Name not Valid" it seems it can complete the code up to the first single quote.



Romefucan

I edited the code a bunch of times due to my own impatience... I think that is there now should be good.... fingers crossed