Syntax error for vlookup function from access database

[baha17]

Hi All,
I found one vllokup function from access data base in earlier posts. Thanks to the author. However, when I tried to adop in my userform as it gets the password line form access data base. But I am getting an syntax error.The error occurs in

adoRS.Open Source:=strSQL, ActiveConnection:=adoCN, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable

Before I alter the code I tested and it was working. My database has password:XXX.
to cut the story short i put the sample as msgbox

Is there anyone can help me?
Thanks for the hand

Option Explicit
Const TARGET_DB4 = "CPUsersDB.mdb"
Dim adoCN As ADODB.Connection
Dim strSQL As String
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
Dim MyConn
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB4
Dim adoCN As ADODB.Connection
Set adoCN = New Connection
'adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
'adoCN.ConnectionString = "K:financemanagement reportingfinance reporting 2002.mdb" 'Change to your DB path
adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Data Source=" & MyConn & ";" _
                & "Jet OLEDB:Database Password=XXX;"
Set adoRS = New ADODB.Recordset
adoRS.CursorLocation = adUseServer
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & _
         "='" & LookupValue & "';"    ' If lookup value is a number then remove the two '
adoRS.Open Source:=strSQL, ActiveConnection:=adoCN, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable
'strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
    
DBVLookUp = adoRS.Fields(ReturnField).Value
adoRS.Close
End Function
Sub susak()
MsgBox DBVLookUp("tblCPUser", "TMId", 5378, "Password")
'"SELECT * FROM tblAllocation WHERE TblID = " & lngID
End Sub

 

[baha17]

I am sorry to replying my own thread but I figured out the error. LookupValue type was wrong in the database. I changed it to "adVarWChar, 18" instead of "Double" it worked fine. Sorry for taking your time.
Thanks