DBVLOOKUP Coding error

[JohnsonFe]

Hi,

I am not able to retreive the date from Access table.Here is below code..Could somebody help me to retreive the data from ACCESS..

Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "U:\workarea\New Project\db1.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 ADODB.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

 

[jsotola]

do you have a typo in your question, or are you trying to retrieve a date value?

you did not mention if you get any data, any errors

 

[JohnsonFe]

Thanks for your response.

I am not able to retreive the numeric value from access. I am getting #value error in the xls sheet.