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

 

[giacomo]

OMG I cannot even read my own code today!!!

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



sorry
Giacomo

 

[VicRauch]

giacomo,
I was in the middle of writing you a message about the order of the parameters when you sent this last reply. But the other thing I wanted to say is, "You have a LOT of patience! Hang in there, and you are doing a great job."

 

[Romefucan]

Code for Cell

Giacamo
The code for the cell is not giving an error, however the code is still returning the description of the first record "AA"

Is there something in the function?

Romefucan

 

[giacomo]

can you open your database up and try the dlookup in a query?
sql would be something like this:

SELECT DLookUp("[Description]","[Device Signals]","[Device Signal]  = 'AAT' ") AS x
FROM ACCOUNT;

Are you getting the same result?

 

[Romefucan]

Query

Giacomo
My database resides on my PC for now, Simple Query in Access finds the correct record and returns the correct description every time. SQL statement you provided results in Wrong Syntax error in Access.

Romefucan

 

[giacomo]

SELECT DLookUp("[Description]","[Device Signals]","[Device Signal] = 'AAT' ") AS x
FROM [Device Signals];

sorry about that, corrected sql is above. I'm at a loss as to why it is not working for you. As I said, it works fine for me (when I enter the parameters correctly). If you PM me an email address I will forward my working example to you. Maybe you can figure it out from there...

hth,
Giacomo

 

[Romefucan]

SQL Statement

Giacomo
Your last SQL statement , when run in a data definition SQL specific Query returns the correct record description 160,000 times. (The number of records in my database)

PS Mail is Romefucan@Yahoo.com

We are close.

Romefucan

 

[Romefucan]

VICTORY !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Giacomo;
You did it!!!!!!!!!!!!!!!!!!! I figured out what was wrong using your example!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*%$&#%$&*^$ B1 Cell statement too many quotes in the wrong places.
It works dammit !!!!!!!!!!!!!!

I greatly appreciate your patience and commend you on your knowlege. Now if I can get past one more hurdle. I will scower the board before posting. Have a drink and celebrate your victory.

Kindest Regards

Romefucan